Calculating hours between 2 date/time columns

Platform discussions Special workflows & use cases
, , ,
1

Hi, I am struggling to find a formula that works across multiple scenarios trying to calculate the time between two date/time columns. For each formula I have tried, it works on some, but not others. Below are screen shots of the results and formulas used.

What I actually need is to calculate work hours between two date/time columns. Examples below.

The number of work hours between Jan. 3, 1:30pm and Jan. 3, 3:00pm should be 1.5 hours. In the picture below, that result is correct. However, the time between Jan. 3, 3:00pm and Jan. 5 10:00am should be 12 hours, but the result is 15.33. Formula in use for this example is,
DAYS({Resolved On},{Reported On}) * 8 + 1
image

If I use this formula, the results are a little different.

(((HOUR({Resolved On})*60+MINUTE({Resolved On}))-(HOUR({Reported On})*60+MINUTE({Reported On})))/60)+((DAY({Resolved On})-DAY({Resolved On}))*24)

image

I’ve tried using workdays and get similar results. Results where one is accurate and others are not. What am I missing here?

I got to this formula, which works for everything that does NOT span a weekend day.

((WORKDAYS({Resolved On},{Reported On}) -1 ) * 8) + (HOUR({Resolved On}) - HOUR({Reported On})) +
(MINUTE({Resolved On}) - MINUTE({Reported On})/60)

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.

3

Hi, Gilles,

Thanks so much for your reply. I just tried it and unfortunately am getting a couple of errors. I see in your final formula that you are calling out columns “Date 1” and “Date”. I tried substituting my “Reported on” and “Resolved on” columns and get a general formula error (the red exclamation mark in the cell with no other details). This is the formula I have applied and the results.

This is what has happened on the ones I’ve reached so far, I can get it 90% of the way there, and then suddenly it breaks. :crazy_face:

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

image
image694×584 24.9 KB

I did modify the formula slightly and believe it is calculating time correctly, even if over the weekend. I welcome any scrutiny of the formula.

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

image
image630×600 27 KB

4

Hi Josh,

It breaks because there is no data in the date columns. Sincerely I don’t know how to avoid the red icon. You would have to take each part of the formula and try to add if statements to prevent it from breaking when the dates are not present. I tried it without success.

This is one of the difficulties of the formula column and one of the reasons why I decided to create the Advanced Formula Booster. I just wrote a blog article on this because this is a pretty common issue.