Find how many days passed formula (not by 24h durations)

Question
1

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
SUBTRACT_DURATION(date, duration,“days”)
it wont work correctly.

from what i understand, i need to use this fomulla
SUBTRACT_DURATION(date, duration,“days”)
but set the time of the older date to 00,

But i cant figure it out, Could you help?

2

Hi, there’s a formula for that:

1 Like
3

Ohh ok let me try that because i was stuck to subtract duration …

4

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

1 Like
5

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”)

6

I would still use timestamp, but break it into parts.

  1. count from the start date to midnight, then make midnight the new start point (difference is now 1 + … call this timestamp 3
  2. 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.

2 Likes