# Date Calculation

Hi all,

Quick question I hope you can help with.

If I have a date column with a list of dates in, what formula do I need to use to work out the number of days between the dates?

for example:

20/12/2019
25/12/2019
29/12/2019

the number of days between  these two dates would be: 5 days for the first one and then 4 days for the second.

I cant just use diff days because that needs to have the dates in separate columns. Maybe I need to create a Date-1 column or something and then use that column to do diff_days?

6replies Oldest first
• Oldest first
• Popular
• The following is a potential solution. I would be interested in understanding other alternatives.

1. Load the Date column as an Integer value. I could not get to_integer to work on date.

 Date Number 20/12/19 43819 25/12/19 43824 29/12/19 43828

2. Create formulas to calculate the difference using Moving_Sum

Current = sum(number) or moving_sum (number , 0,0,date )

Previous = moving_sum (number , 1,-1 ,date )

Diff = current - previous

Like 1
• Damian Waldron thanks. I'll give it a go!

Like
• You can achieve this without having to load the number of the date into the database by using the diff_days function, in which case, the formula would be:

Current = sum(diff_days(date,today())) or moving_sum (diff_days(date,today()) , 0,0,date )

Previous = moving_sum (diff_days(date,today()) , 1,-1 ,date )

I've used today as the reference point, but you can use any arbitrary date as the reference point.

Like 2
• nice one. Much cleaner solution.

Like
• James Belsey this worked perfectly, thank you

Like
• Marc Price Great to hear and thanks to Damian Waldron for the initial suggestion

Like
Like Follow