Hello Community,
Does anyone have an idea why this date formula is returning this value?
I want to subtract from Today’s date the start date of a formula, my basic formula is:
FORMAT_DATE(TODAY(),“DD-MMM-YY”)-FORMAT_DATE({Start Date},“DD-MMM-YY”)
If, Today’s date is 14-Nov-23 and the start date is 1-May-23, why is the formula returning 13? I would expect the answer to be 197.
Hello Andrew, I don’t know why it does that, but what I know is that you should use the DAYS function instead.
You also don’t need a Today’s date column, you can insert TODAY() in your formula and it will return today’s date. Also, TODAY returns a date and time, so your answer will have decimals, if you don’t want decimals you can use ROUNDDOWN.
So paste this into your formula column and it should work.
ROUNDDOWN(DAYS(TODAY(),{Start Date}),0)
Hi Bob, I really appreciate the reply and your advice helped me get the answer. I had quite a long formula but when getting the strange ‘13’ answer split the components of the formula into different columns to check each part in turn,
The final formal was:
ROUND((ROUNDDOWN(DAYS(TODAY(),{Current Baseline - Start Date}),0))/(ROUNDDOWN(DAYS({Current Baseline - End Date},{Current Baseline - Start Date}),0))*100,0)
which seems to work
Many thanks