Help

DATETIME_DIFF result is unexpected

Topic Labels: Dates & Timezones
1744 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Mireille_KAYIJA
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,

I get a date in a field, using Rollup and Max (value). Now I would like to know the number of day from now, usinv DATETIME_DIFF

First column is the rollup with max value. Seem to work since I have a summary MaxDate. Second colum is formula Today, and third is the calculation of the difference between two column before.
image

Formula of column Today
image

Formula of column Calculation
image

Why do I got such an important result, instead of 0 for instance for 12/05/2021

Any idea ?

Thank you

2 Replies 2
pcbowers
6 - Interface Innovator
6 - Interface Innovator

@Mireille_KAYIJAMAHE It took me a bit to recreate your problem, but after a little trial and error, I was able to recreate your results:

image

As you can see, the Starting Date Rollup pulls in the Date from the Dates Table, Today’s date is calculated, and then the calculation is done, yet is off for some reason.

The reason I was having trouble is because I was reading your dates in Local format which is US, not European format. In fact, recreating your problem showed me exactly what needed to be changed: your Today function is being formatted in the European style but is being read in the US style (MM/DD/YYYY).

The solution here is to make sure your dates are formatted properly. You can do this by going to your Dates table (or your Activity journal, wherever you are pulling the dates from) and formatting the field to use European dates:

image

From there, also do the same on the current table where you are doing the calculations. Make sure to do this both for the Starting Date Rollup (from Activity journal) and the Today field. Note, you probably won’t see the formatting options on the Today field. This is because you used the DATE_FORMAT() function which converts it into a string. However, you do not want a String type, you want a Date type. To do this, remove the DATE_FORMAT() function from your formula:

TODAY()

Then you will be able to change formatting options appropriately:

image

With this complete, your Calculation field should be working as requested.

Currently, your Calculations field is returning a positive number for dates in the past. This means it is saying how many days have passed since this activity. If you need the opposite (i.e., a due date), swap the order of parameters in your formula:

DATETIME_DIFF({Starting Date Rollup (from Activity journal)}, Today, "days")

You can also remove the need for the Today field entirely by adding the TODAY() function directly to the formula:

DATETIME_DIFF({Starting Date Rollup (from Activity journal)}, TODAY(), "days")

As you can see, this returns a negative number showing that March 1st is in the past:

image

Mireille_KAYIJA
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you very for your anwer!! I have to test it!!! I will tell you if it works