Help

Re: Days between dates

4371 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Max_Cordell
5 - Automation Enthusiast
5 - Automation Enthusiast

Can anyone tell me the formula for calculating the number of days between “date logged” and “date completed”. And if the formula could be expressed as working weekdays only that would be icing on the cake! Many thanks

22 Replies 22

You would need to run your datetime_diff function in the table where your Calls are located (using a lookup field for onboarding date if necessary). Then, you could bring the results to the current view with another lookup field in the same way you have brought in the Dates of Call IDs.

We’ve also just added a WORKDAY_DIFF formula! Documentation here.

It takes in a start date, end date, and optional list of holidays, like so: WORKDAY_DIFF({Assignment date}, {Due Date}, '2017-09-04, 2017-10-09, 2017-11-10'), and returns the number of working days between the start date and end date, inclusive.

For those of you familiar with Excel or Sheets, it’s pretty much isomorphic to the NETWORKDAYS formula in those products.

I’m in the same boat. Here’s what I’m trying to run:
DATETIME_DIFF([Start date], [End date, ‘weeks’)

However, I’m still getting an error message.

So this leads me to two fundamental questions:

  • Can I deduce the difference between two fixed dates (or does it have to the number of days, weeks, etc. between a past date and the present)?

  • If so, how can I fix my formula?

Thanks!

By

do you mean you aren’t allowed to save the field but instead see a message beginning, ‘Sorry, there was a problem saving this field’? Or do you mean you can save the field correctly, but when you try to run the formula by populating the fields, you get an #ERROR message – or a NaN error?

That’s right, it says ‘Sorry, there was a problem saving this field’?

In that case, it’s because you’re using the wrong characters to enclose the field names. Give this a try:

DATETIME_DIFF({Date Depleted},{Date Purchased},'days')
Geoff_Girvitz
4 - Data Explorer
4 - Data Explorer

That’s exactly what I needed. Thanks very much, @W_Vann_Hall. You’re aces!

Robert_Matyszew
4 - Data Explorer
4 - Data Explorer

There’s function WORKDAY_DIFF

Diane_Bevington
4 - Data Explorer
4 - Data Explorer

Thanks so much @Martha_Creedon !! I searched google for the same problem and your response really helped me out quickly.

Mark_Baker1
4 - Data Explorer
4 - Data Explorer

I am trying to get the numbers of days difference between a date cell and a formula cell using IF({Return Solved}=1, DATETIME_FORMAT(SET_TIMEZONE(NOW(), ‘New Zealand/Wellington’), ‘D/M/YYYY’))

I am trying to use DATETIME_DIFF({Date of Return}, {Date Resolved}, ‘days’) but it is not showing the correct amount of days?

Any ideas?