Mar 21, 2016 09:33 AM
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
Mar 22, 2016 05:22 PM
Hi Max - wouldn’t that be DATETIME_DIFF?
See https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#date_and_time_funct...
Mar 24, 2016 05:14 AM
Exactly what I needed. Thanks Martha
May 11, 2016 11:44 AM
Is the second part of the original query possible – to calculate the number of weekdays via DATETIME_DIFF or another formula?
May 16, 2016 12:18 PM
Hi Mike -
Probably you can get that with more elaborate calculations, but I haven’t tried to work that out. I don’t see that Airtable yet has the functionality that Excel provides with NETWORKDAYS.
Martha
Jul 14, 2016 12:31 AM
Old thread; but hey the topic still has merit
I tried this formula DATETIME_DIFF([date1], [date2], ‘units’) and I get an error message so tried this:
DATETIME_DIFF([Date Depleted], [Date Purchased, ‘days’) and so I am assuming I am doing it incorrectly.
Mar 07, 2017 09:35 AM
I would love to see Business days added to the Supported unit specifiers for DATETIME_DIFF
Aug 30, 2017 10:45 AM
Some of you in this thread might be happy to know that we’ve implemented a WORKDAY()
function!
The format is WORKDAY(startDate, numDays, [holidays])
and it returns a date that is numDays
working days after startDate
. Working days automatically exclude weekends, and you can include an an optional list of holidays
as a comma-separated string of ISO-formatted dates, e.g.
WORKDAY({Launch date}, 100, '2017-09-04, 2017-10-09, 2017-11-10')
Oct 24, 2017 03:48 PM
Excellent! Could we please have the inverse function as well (a function that will return the number of workdays/workhours between two date/time fields)?
Oct 25, 2017 12:45 PM
Does anyone know how to use the datetime_diff function with multiples dates in one cell that are then compared to one date? Example of the picture below.
Oct 27, 2017 10:37 AM
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.
Jan 02, 2018 03:18 PM
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.
Mar 30, 2018 06:59 AM
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!
Mar 30, 2018 07:30 AM
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?
Mar 30, 2018 08:41 AM
That’s right, it says ‘Sorry, there was a problem saving this field’?
Mar 30, 2018 08:58 AM
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')
Apr 03, 2018 06:37 AM
That’s exactly what I needed. Thanks very much, @W_Vann_Hall. You’re aces!
Oct 15, 2018 12:26 AM
There’s function WORKDAY_DIFF
Oct 21, 2018 04:04 PM
Thanks so much @Martha_Creedon !! I searched google for the same problem and your response really helped me out quickly.
Aug 05, 2019 08:57 PM
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?