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.
Formula of column Today
Formula of column Calculation
Why do I got such an important result, instead of 0 for instance for 12/05/2021
Any idea ?
Thank you
@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:
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:
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:
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:
