Hi, i needed some help with a formula for Duration subtraction. More specifically, I need to find how many days have passed from an older date to today, but i dont want it in 24h durations, For example, im using this formula
NUMBER(FORMAT_DATETIME_LOCAL(pageVars.now, “D”))-NUMBER(FORMAT_DATETIME_LOCAL(data.Lender_data1.Last_payment, “D”))
and it works,
But the downside, is that if i want to subtract these two dates 1/4 30/3 it will give wrong result.
On the other hand, if i use the formula
it wont work correctly.
from what i understand, i need to use this fomulla
but set the time of the older date to 00,
But i cant figure it out, Could you help?
Hi, there’s a formula for that:
Ohh ok let me try that because i was stuck to subtract duration …
Hi, I think its possibly even easier - use timestamp. It gives you date time in ms from 1970, so you can easily subtract one from the other.
Timestamp() - timestamp("your date/time) = xxx ms
There are 86,400,000 milliseconds in a day
This way, is definitely better, but still, it doesn’t do what i wanted.
for example, if the one day, is 12/2 13:00 , i need it to count(add one), when the day becomes 13/2 00:00 meaning that i dont want the time to take part in deciding whether or not a day has passed.
For that reason your way doesn’t fit @Phil_Evans
I did this, to test it with minutes and it works but will it work for days also?
DATETIME_DIFFERENCE(SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(pageVars.now, 00, “second”), 000, “millisecond”), SET_DATETIME_COMPONENT(SET_DATETIME_COMPONENT(data.Lender_data1.Last_payment, 00, “second”), 000, “millisecond”),“minutes”)
I would still use timestamp, but break it into parts.
- count from the start date to midnight, then make midnight the new start point (difference is now 1 + … call this timestamp 3
- Do date Roundup((timestamp1-timestamp3)/86400000,0)
So effectively its part 2 + 1, unless the first timestamp falls exactly on the end of the day.