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.

Days	Start	End
Mon-Fri	8:00	16:00
Sat-Sun	9:00	13:00

<tbody>
</tbody>

Start time	End time	Total work hours
1/26/2015 12:37	2/2/2015 5:59	???????????????????

<tbody>
</tbody>

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!

 

[adam087]

Hi and welcome to the board

Does this work for you?

Excel 2012
	A	B	C	D	E
1	Start Time	Finish Time	Total Work Hours		Holidays
2	Mon 26-Jan-15 12.37 PM	Mon 2-Feb-15 05.59 PM	48		27-Jan-15
3					03-Feb-15
Sum

Cell Formulas
Range	Formula
C2	=(NETWORKDAYS.INTL(A2, B2, "0000011",$E$2:$E$3 )*8)+(NETWORKDAYS.INTL(A2, B2, "1111100", $E$2:$E$3)*4)

/AJ

 

[Myrko]

Hi and thanks for quick reply!

When I've tried your formula in this exact example, I've got 52 hours as a result (I did not input any day as holiday). When I calculated manually, there is 3:23 work time on Monday, there are 4 full work days (Tuesday through Friday), and 4 hours each day of weekend between two given dates. That should total 43:23 of work time...

I don't see how given formula includes time of the start/end day in the calculation (which is very needed in this case).

 

[barry houdini]

Hello Myrko, which version of Excel are you using?

 

[Myrko]

Oh sorry, I have forgotten to mention it. I'm using MS Excel 2010 at work and Office 2013 at home.

 

[barry houdini]

OK, here's one way to calculate the hours.....

Construct a 4 column Table like this in E2:H8

Day	Start	Finish	Duration
Monday	8:00	16:00	8:00
Tuesday	8:00	16:00	8:00
Wednesday	8:00	16:00	8:00
Thursday	8:00	16:00	8:00
Friday	8:00	16:00	8:00
Saturday	9:00	13:00	4:00
Sunday	9:00	13:00	4:00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Now assuming start time/date in A2 and end time/date in B2 you can use this formula in C2

=SUMPRODUCT(NETWORKDAYS.INTL(A2,B2,SUBSTITUTE("1111111",1,0,{1;2;3;4;5;6;7}),J$2:J$10),H$2:H$8)-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)-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 J2:J10 contains a list of holiday dates

Format C2 to show "elapsed hours", i.e. with custom format [h]:mm

Note: I'm assuming that your start or end dates won't be on holidays - if they might be then you need an additional tweak to the formula

 

[Myrko]

I don't have time to check the formula now but the thing is that the start and end date could be on holidays as well so I could use your help on that too if you have time

Btw should this formula be working on any date range (no matter if it is 2 days or 55 days)? Because the events that I'm trying to track might last longer than one week.

Thanks in advance!

 

[barry houdini]

Yes, it will work for any start and end dates/times as long as B2 >= A2

I'll look at amending for the possibility of start/end on holiday dates......

 

[Myrko]

Many thanks for your efforts

By the way, you don't need to waste your precious time to explain me that thoroughly. I consider that I've learned a lot about excel (most of the thanks to sites like MrExcel) so I think that I'm pretty adroit in Excel I usually don't use any formula before I have full understanding of the way it works.

The only field which I am totally clueless about is OLAP and direct connection to databases but I didn't need to use it yet.

 

[Myrko]

Update:

I think I've managed to bypass the problem! On your suggested formula I have added patch which worked on examples where starting or ending date matches holiday.

Formula which worked is:

=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:K,2,FALSE),-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(VLOOKUP(INT(B2),J:K,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)))

I've put bold+underline to the changes which were made in order to make formula work with any date entered as start/end point.