Calculating days between two dates

I think Knack could use one more date function to calculate the number of days between two dates.

Subtracting two dates doesn't quite work because the system uses both date and time for each field even if "ignore time" is chosen in settings. The result is rarely an integer because the times will be different, so it's possible that January 2 - January 1 will result in 0, 1, or 2 when rounded. This is confusing and likely to cause unexpected results.

I have a workaround that calculates the number of days between two dates irrespective of the time:

round(currentTime()/86400000-.5,0) - round({Date}/86400000-.5,0)  where "Date" is the name of your date field. 

Note that in the Knack builder "is during the 1 previous day" means "during the 24 hours up to this moment" i.e. if it's currently 3pm and Date is 2pm on the same day, it is considered to be during the previous 1 day.  

Therefore if you need to test for what is really the previous day (anytime on Monday if this is Tuesday), then use the equation above and test for the value "1".

 

1 Like