Simplicty and flexibility!

EDATE Excel function equivalent in DataEase

Started by Robert Smith

You will need to Sign In to be able to add or comment on the forum!

EDATE Excel function equivalent in DataEase

Hi All,
Does anyone know of a function in DataEase that is functionally equivalent to the =EDATE function in MS Excel ?
This is an extremely useful function used to calculate a date when you add or subtract any number of months to any date.

I've uploaded a sample spreadsheet demonstrating how the EDATE function works

I'm not a DataEase expert so if anyone out there can help, I would be grateful and I know others would appreciate it

Thanks All !

Written by Robert Smith 24/06/21 at 05:54:04 Not product specific.

Re:EDATE Excel function equivalent in DataEase

There is no function to do that but there is several ways to achieve the same.

In DataEase you can add and subtract days from any date and it will return the correct date.

Obviously with months being so "stupidly" configured i.e. 28, 29, 30 or 31 days that is not always a good way.

Then you can deconstruct and re-construct a date.

Month() and Year() and Day() will give you the individual numbers.
YOu can then do your maths on months but have to take care of the carry overs like 6-11= -5 then subtract one from year and -5 from month etc.

When you have the correct year,month,day simply rebuild the date with
DATE(MM,DD,YY)

http://www.dataease.com/dg3_HelpView/?PageID=10056&field1=*Date*

Written by DataEase 25/06/21 at 16:09:24 Not product specific.

Re:Re:EDATE Excel function equivalent in DataEase

Thanks Very Much!  I appreciate you taking the time to reply and advise.

Your suggestion is what I thought would be the way to achieve the same result.  I'll try to understand the algorithm MS uses in their EDATE function to get the very accurate result it does and then apply it using the Date functions you mentioned.

The great thing is that if DataEase doesn't have the identical function, we can most always build a solution that gets the same result.

We operate a business where expiration dates are very important so we'll get it to work.

If your team is considering new functions for a future release, this may be a good one (slight Hint ??).

When I build a work around, I'll come back to the thread and post it.

Cheers

Written by Robert Smith 26/06/21 at 01:23:57 Not product specific.

Re:Re:Re:EDATE Excel function equivalent in DataEase

Hi again Robert.
In a way I think that the way DataEase should work - had to test if it actually worked like this but it didn't.

12/01/32 + 00/03/00 should be 12/04/32
12/01/32 - 00/03/00 should be 12/10/31

Etc.

I will check with the boys/girls and we discussed it and decided to include a function in DEOS() that can add months, years etc.

DEOS is the big exciting functional news in DE9.

It is like our own operating system inside DataEase and will be worth a study on its own.

The format will be.