DateAdd function

[camilo004]

Dear Isomorphics,

When I need to have a column in ListGrid with a numeric calculation, I usually use the "Add Formula Column..." option from the ListGrid context menu. Recently, I found myself needing to perform calculations between dates within those formula fields and discovered that the dateAdd() function can be used. However, when trying to perform arithmetic operations with this function, I noticed that when I add a number, I get a dot as a result, but when I perform other operations such as subtraction, multiplication or division, I get a numeric result.

In the attached images you can see the behaviour described above.

I would like to know which of these two behaviours is expected and if it is correct that the dateAdd() function returns a numeric result. Also, I would be interested to know if it is possible to get a result in the form of a date instead of a number in these calculations.

We are working with version 13.0

[Isomorphic]

Hi Camilo,

dateAdd() returns a modified Date, and you cannot meaningfully add or subtract a number from a Date.

If you were trying to modify the date, like reduce it by two days, then you pass any such numbers to the dateAdd() function.

Note you could also call dateAdd twice. So for example, you might use something like this to add a month but then subtract two days:

dateAdd(dateAdd(D, "m", 1), "d", -2);

[camilo004]

Hi Isomorphic.

This is clear to us as developers, but if an end user is writing the formula for the dateAdd function incorrectly, how can they know that the formula is wrong if the function is showing them a result when they perform some of the arithmetic operations with the function?

For example:

dateAdd(D, "m", 1) - 2.

[Isomorphic]

They would know because the result is very obviously wrong. In what other way would you expect them to know?

Note that dateAdd() is an Excel function, and we just implemented the same behavior. So if a user can use Excel formulas, they can use this feature.

Still, we have plans to further enhance this area, adding an option for an AI to write a formula for you, based on a natural language description. If you are interested in that functionality, please use the Contact form to get in touch with us about a Consulting POC.