DATETIME_DIFF result is unexpected

@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:

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