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
  • Newest first
  • Active threads
  • 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
  • Status Answered
  • 5 mths agoLast active
  • 6Replies
  • 51Views
  • 3 Following