Sum of work hours between two dates with changing shifts

[Myrko]

Hello,

I've tried how to calculate how many work hours were there between two dates when I have one work time during regular workdays and another work time during weekend (let's say Saturdays and Sundays have same work time for this example).

So I want to calculate how many working hours passed from 1/26/2015 12:37 PM to 2/2/2015 5:59 AM when work time from Monday to Friday is from 8:00 AM to 16:00 PM and on weekends work time is from 9:00 AM to 13:00 PM.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Days[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Mon-Fri[/TD]
[TD]8:00[/TD]
[TD]16:00[/TD]
[/TR]
[TR]
[TD]Sat-Sun[/TD]
[TD]9:00[/TD]
[TD]13:00[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Start time[/TD]
[TD]End time[/TD]
[TD]Total work hours[/TD]
[/TR]
[TR]
[TD]1/26/2015 12:37[/TD]
[TD]2/2/2015 5:59[/TD]
[TD]???????????????????[/TD]
[/TR]
</tbody>[/TABLE]

I've been using some formula to calculate total work hours within two given dates but the work time was the same every day of week.

Also, could there be some column in spreadsheet where I can input list of holidays so a formula excludes those listed days from calculations?

Thanks in advance for help!

 

[barry houdini]

That's a clever way to do it, I was going to suggest using a COUNTIF to determine whether start or end dates are in the holiday range, e.g.

=SUMPRODUCT(NETWORKDAYS.INTL(A2,B2,SUBSTITUTE("1111111",1,0,{1;2;3;4;5;6;7}),J$2:J$10),H$2:H$8)-IF(COUNTIF(J$2:J$10,INT(A2)),0,MEDIAN(VLOOKUP(TEXT(A2,"dddd"),E$2:H$8,2,0),VLOOKUP(TEXT(A2,"dddd"),E$2:H$8,3,0),MOD(A2,1))-VLOOKUP(TEXT(A2,"dddd"),E$2:H$8,2,0))-IF(COUNTIF(J$2:J$10,INT(B2)),0,VLOOKUP(TEXT(B2,"dddd"),E$2:H$8,3,0)-MEDIAN(VLOOKUP(TEXT(B2,"dddd"),E$2:H$8,2,0),VLOOKUP(TEXT(B2,"dddd"),E$2:H$8,3,0),MOD(B2,1)))

That should get the same results as your version

 

[Myrko]

This is solid base now, my next task is to apply this kind of counter for working hours for different objects which have different working schedules (on regular days, weekends, even on holidays). I've intended to make some kind of ID's which will pull working hours for particular object for every situation. I would never make it without your formula. Many thanks once again!

 

[Myrko]

That's a clever way to do it, I was going to suggest using a COUNTIF to determine whether start or end dates are in the holiday range...

I've gone a little bit further with the formula and now I'm trying to include special work time on holidays:

=SUMPRODUCT(NETWORKDAYS.INTL(A2,B2,SUBSTITUTE("1111111",1,0,{1;2;3;4;5;6;7}),J$2:J$10),H$2:H$8)+IFERROR(VLOOKUP(INT(A2),J:M,3,FALSE)-MEDIAN(VLOOKUP(INT(A2),J:M,2,FALSE),VLOOKUP(INT(A2),J:M,3,FALSE),MOD(A2,1)),-MEDIAN(VLOOKUP(TEXT(A2,"dddd"),E$2:H$8,2,0),VLOOKUP(TEXT(A2,"dddd"),E$2:H$8,3,0),MOD(A2,1))+VLOOKUP(TEXT(A2,"dddd"),E$2:H$8,2,0))+IFERROR(MEDIAN(VLOOKUP(INT(B2),J:M,2,FALSE),VLOOKUP(INT(B2),J:M,3,FALSE),MOD(B2,1))-VLOOKUP(INT(B2),J:M,2,FALSE),-VLOOKUP(TEXT(B2,"dddd"),E$2:H$8,3,0)+MEDIAN(VLOOKUP(TEXT(B2,"dddd"),E$2:H$8,2,0),VLOOKUP(TEXT(B2,"dddd"),E$2:H$8,3,0),MOD(B2,1)))

where J:M contains Holiday date as first column and other three columns are the same like for the area E:H (start, finish and duration columns).

Now this formula was written in cases where Holidays fall on the first and/or last day of the period but I'm trying to find way so the formula works even when Holidays are in between starting and ending date. Could you spare some time and assist me on this matter please?

 

[Myrko]

Subtracting COUNTIFS(J:J,">"&INT(A2),J:J,"<"&INT(B2)) multiplied by number of working hours for Holidays might do the job after all.