WORKDAY_DIFF return hours and minutes, not whole days

[McKenna_Johnson]

I am having trouble understanding the WORKDAY_DIFF and DATETIME_DIFF. Primarily I need it to return hours and minutes within 5 working days. Not just whole days with the WORKDAY_DIFF and not just total hours and minutes including weekends with DATETIME_DIFF.

Example: Start a project at 4:25 pm Friday and finish at 9:40 am Monday, I would like my Turn Around column to say 2 hours and 15 minutes (2:15). I do not need it to round up to the nearest whole hour.

I would appreciate any help you can give.

[Matthew_Neal]

I know this is old, but this is something critical I need. I also can’t find any decent documentation around WORKDAY_DIFF. The return value between two dates changes based on the time of day as well?

[McKenna_Johnson]

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.

[Matthew_Neal]

That’s great, thanks so much for the reply! At this stage, I think I’m abandoning the Workday_diff function, as it’s not exactly what I need. Honestly, what I’m really after is the time in minutes between two dates, but only the business hours. So Monday - Friday, 9am - 5pm.

I appreciate your thorough and quick response! I’ll take look around like you recommended.

[DagimAsnake]

If you have solved this thing can you provide me with the answers

my question is 

---

@McKenna_Johnson wrote:

I am having trouble understanding the WORKDAY_DIFF and DATETIME_DIFF. Primarily I need it to return hours and minutes within 5 working days. Not just whole days with the WORKDAY_DIFF and not just total hours and minutes including weekends with DATETIME_DIFF.

Example: Start a project at 4:25 pm Friday and finish at 9:40 am Monday, I would like my Turn Around column to say 2 hours and 15 minutes (2:15). I do not need it to round up to the nearest whole hour.

I would appreciate any help you can give.

---

How to Calculate the working days and return the value in terms of day and hours like "4.75"

[clairehunter]

REALLY need this feature as well - i.e. time diff between work days hours OR days to 2-3 decimal places.