WORKDAY_DIFF return hours and minutes, not whole days
Hey Matthew, welcome to Airtable!
I have really enjoyed Airtable for the past few years but this is the one function that simply refuses to work how we need it to! It mostly has to do with the formatting tab in the formula field only offering variations of of the “hh:mm:ss” date/time format and not “MM/DD/YYYY hh:mm:ss”. WORKDAY_DIFF also doesn’t include an option to set the length of your work day, e.g. 8hrs or a full 24 hour cycle so its unable to calculate the work hour difference between your start time and your end time.
I fear the solution might lie in the scripting function where you could craft an automation or button to run a script that preforms all the necessary calculation and delivers you a result in a number field. You may also have better luck looking for time/date calculation forum topics if you search for topics that have to do with billing for companies that charge by the hour or payroll.
For my particular issue I could only ever get WORKDAY_DIFF to consistently deliver a day count as an integer and even then it doesn’t calculate by the hh:mm so a task could be submitted at 10am on Monday and returned 8am Tuesday but it would still return a difference of 2 days even though it was returned only 22 hours later, not +24 hours. I then created another field with and IF statement to deliver a quick message describing the the turnaround time/expectation. I then hide the “Turnaround Time - Days” field so my team only sees the short message.
My “Turnaround Time - Days” field:
WORKDAY_DIFF((DATETIME_FORMAT({Start Time}, ‘MM/DD/YYYY’)), (DATETIME_FORMAT({ End Time}, ‘MM/DD/YYYY’)))
My “Turnaround” field:
IF({Turnaround Time - Days} = 1, “ :star2: Same Day”, IF({Turnaround Time - Days} = 2, “ :sparkles: Next Day”, “+2 Days”))
Not the most helpful post but hopefully you’re not left searching in the wilderness for an solution.
