Formula for calculation time differences by business hours

Paul Newcome

Excellent. I'll get to work on automating it.

Paul Newcome

Take a look at THIS SHEET and let me know if you are able to get it working for you.

Debbie J

Looks good

Per the attached sheet

I do see one item for W867587 The 1/25/20 (Sat) was changed to 1/27 which is a Monday. The create time should be changed to 8:00 am, but it is showing as 11.13

Besides that it looks great

Paul Newcome

Take another look. I modified the formula in that column, so it should be pulling correctly now.

Paul Newcome

Oh. And the [CalcDiff] column is what you would use to pull metrics such as average times or total times.

Debbie J

Hi Paul everything looks great except for the Calcdiff column

Your Calc is not adding the days and maybe the samples I gave you did not have those examples

See the below formula and advise if I got this right. A 200% thank you on helping me as I would not have formatted this by myself. Especially knowing what the Excel formula looks like.

Calc Diff Column suggestion

=IF(SUM(StartDate@row – EndDate@row) > 0.99, ((StartDate@row - EndDate@row) * 9 + (EndTime@row - StartTime@row)),(EndTime@row - StartTime@row))

Paul Newcome

The days are calculated in the [End Time] column.

If the [Time Difference] column is correct, then so is the CalcDiff column as the [Time Difference] column pulls directly from that one.

Debbie J

I see now...

Thank you again !! so far looks great

Paul Newcome

Happy to help. 👍️