0

I'm trying to calculate the total number of hours between two columns of start and end times but only those hours that fall between 9:00am and 5:00pm, Monday to Friday.

For example, my data looks something like this:

Date      | Start Time | End Time | Duration
Wednesday | 930        | 1400     | 4.50
Thursday  | 930        | 1300     | 3.50
Saturday  | 800        | 1600     | 8.00
Sunday    | 1000       | 1200     | 2.00
Tuesday   | 730        | 1400     | 6.50
Friday    | 930        | 1900     | 9.50

So I need to add up how many hours this space was used, but only those hours from 9:00am to 5:00pm and only between Monday to Friday. Is there a formula I could construct for this?

Improve this question
2
  • the IF condition should help
    – Scorpion99
    Apr 19, 2016 at 14:15
  • I found my answer after a lot of tinkering. I had to set reference cells with start and end times and add a new helper column, but I used this: =IF(WEEKDAY(I10,2)<6, (F10-((IF((('COU Summary'!$G$4-(TIME(INT(D10/100),D10-INT(D10/100)*100,0)))*24)>0,('COU Summary'!$G$4-(TIME(INT(D10/100),D10-INT(D10/100)*100,0)))*24,0))+IF((((TIME(INT(E10/100),E10-INT(E10/100)*100,0))-'COU Summary'!$H$4)*24)>0,((TIME(INT(E10/100),E10-INT(E10/100)*100,0))-'COU Summary'!$H$4)*24,0))), 0)
    – Justen
    Apr 19, 2016 at 15:05

1 Answer 1

Reset to default
0

You can use the below formula.

=IF([End Time]>TIME(17,0,0),TIME(17,0,0),[End Time])-IF([Start Time]<TIME(9,0,0),TIME(9,0,0),[Start Time])

It will replace End Times with 17:00 and Start Times with 09:00 if the End Time is later than 5 PM or the Start Time is earlier than 9 AM. If the Start Time and End Times are on different sides of midnight (e.g. Start = 17:00, End = 04:00) you will need to adjust the formula to account for that.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .