Calculating hours between 2 date/time columns

Platform discussions Special workflows & use cases
, , ,
2

Hi Josh,

Here is one way of doing it.

First, you need to count the number of full days between the 2 dates. For this you remove the time portion of the dates and use the DAYS function to calculate the difference of days. To remove the time portion, you use FORMAT_DATE.

DAYS(FORMAT_DATE({Resolved On},"YYYY-MM-DD"),FORMAT_DATE({Reported On},"YYYY-MM-DD"))

So if both dates are on the same day, you get 0. If Resolved On is on the next day, you get 1.

You now have the number of full days. To get the hours, you simply multiply it by 8 since you have a 8-hour workday.

Then you need to take into account the time of the 2 dates, ie you need to add or substract hours based on them. For instance if Resolved On is 3:30PM and Reported On is 2PM, you need to add 1.5 hour. If the other way around you need to substract 1.5 hour.

This part of the calculation can be done with TIMEVALUE a function that converts a time into a portion of a day :thinking:. Since we are looking for hours, you then convert this portion of a day into hours by multiplying it by 24 :bulb:.

(TIMEVALUE({Resolved On})-TIMEVALUE({Reported On}))*24

Now you add the 2 calculations together to get the desired result.

DAYS(FORMAT_DATE({Resolved On},"YYYY-MM-DD"),FORMAT_DATE({Reported On},"YYYY-MM-DD"))*8 + (TIMEVALUE({Resolved On})-TIMEVALUE({Reported On}))*24

This works… as long as there are no week-ends in between the 2 dates.

What about week-ends? Week-ends only affect the full-day calculation. For this, you need to change the function to count the number of full days from DAYS to WORKDAYS.

One more thing :slightly_smiling_face:. I am not sure why but DAYS(“2024-01-10”,“2024-01-10”) returns 0 and WORKDAYS(“2024-01-10”,“2024-01-10”) returns 1, so you need to deduct 1 when using WORKDAYS.

The final formula becomes:

(WORKDAYS(FORMAT_DATE({Resolved On},"YYYY-MM-DD"),FORMAT_DATE({Reported On},"YYYY-MM-DD")-1)*8 + (TIMEVALUE({Resolved On})-TIMEVALUE({Reported On}))*24

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.