I am trying to set a formula to calculate elapsed time respecting work hours/days. I've looked around for an answer to this particular issue and have found some others that got me part of the way there, but not completely. Here's what I've got:
I will have a submission date and time (month, day, year, hours, minutes) and a review date and time.
Work hours are 08:00 - 12:00 and 13:00 - 17:00, Monday through Friday. There are holidays as well, which I'll have in a list off to the side.
I've attached an example sheet with the correct turn times. I'd like the "Turn time" shown in Column F to be in either hh:mm format or as a number giving number of hours plus number of minutes as a fraction of an hour (i.e. time elapsed between 8:30 and 10:45 is 2.25). No preference on which - just something I can easily see what the time elapsed is and something I'll be able to do calculations on. I'll be averaging these turns times to give daily and monthly averages, and then calculating a bonus based on those averages.
Any help would be greatly appreciated. Like I said, I saw some that calculated based on work hours, but I only found one that considered specific shifts and it was pretty confusing on how the formula worked, and when I copied it over to my sheet and substituted my own numbers the calculations were off.
Bookmarks