Determining Time Between Two Dates With Time Within Work Hours

So I have two columns. Column A has a start date and time, and column B has end date and time. I am wanting to have column C to have the time between start and end times. Also, a work day is only from 8:00 am to 5:00 pm, so I don't want to have the after hours counted in the calculation. So for example, if I start on 10/7/2015 8:00 am and end at 10/8/2015 9:30 am the result should be 10.5 hours. In column C I want the output to be formatted as 10:30.

This is how I managed to do it. A few helper columns, maybe you can use this to find a more compact solution.
Cell formatting must be precise - time and date where needed.

Do you want the weekends to be excluded as well (Saturday, Sunday)?

Originally Posted by cbatrody

Do you want the weekends to be excluded as well (Saturday, Sunday)?

Totally forgot about those. Yes, I want those excluded.

Hi,

See the attached sample file. I have created the following formula that calculates the time difference between two dates (along with time) excluding weekends & holidays & also time before 8:00 AM and after 5:00 PM.

Please Login or Register  to view this content.