Count working hours between 2 dates excl weekends either with formula or function

[kirbyfez]

Hey all,

I know I can find the answer to my question on quite a few places but I've tried most I find and they just don't work.
Don't mind if its a formula or function though.

example of what I tried for example is:
=IF(NETWORKDAYS(A1,B1)=1,(MIN(Z1,MOD(B1,1))-MAX(Y1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(600/1440)+(Z1-MAX(MOD($A$1,1),Y1))+MIN(MOD($B$1,1),Z1)-Y1)
A1 is start time
B1 is stop time
Y1 is start of work day (8:00)
Z1 is end of work day (17:00)
(Thinking it does not work because of the starttime and endtime is difference, that it covers 2 days in a way, 2pm-3am)

My issue is that our starting time is 2PM and end time is 3AM and the solutions I've found just do not work at all.

I was thinking either a function which does =TimeDiff(Date/timeStart, Date/timeEnd) or just link to a start date and end date in a formula.

I need the code/formula to exclude weekends completely.
Any ideas guys?

Hoping someone can help me out here!

Best regards
Christian

 

[kirbyfez]

Hi,

Seems like this one works pretty wekk. Issue however is for weekends.. I tried changing the times a bit and it still counts during weekends after 3am saturday. Suggestions?

Start Date	End Date	Hours Difference	Minutes Difference
9/27/2014 2:00	9/27/2014 7:00	5	300

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

 

[skorpionkz]

Can we made assumtion that there will be no "incorrect" time, outside of the working hours?

 

[skorpionkz]

check this formula, didn't test it in any possible situation though

Excel 2010
	A	B	C
1	Start Date	End Date	Hours Difference
2	23/09/2014 01:00:00	24/09/2014 17:00:00	18.00
3	19/09/2014 23:00:00	22/09/2014 03:00:00	4.00
4	22/09/2014 15:00:00	22/09/2014 16:00:00	14.00
5	22/09/2014 03:00:00	24/09/2014 16:00:00	28.00
Sheet1

Cell Formulas
Range	Formula
C2	=IF(NETWORKDAYS(A2,B2)<=2,0,NETWORKDAYS(A2,B2)-2)*13+("24:00"-MOD(A2,1))*24-IF(MOD(A2,1)*24<=3,11,0)+(MOD(B2,1))*24-IF(MOD(B2,1)*24>=14,11,0)

 

[skorpionkz]

IGNORE MY LAST POST PLEASE
it didn't work for the same day

Excel 2010
	A	B	C
1	Start Date	End Date	Hours Difference
2	23/09/2014 01:00:00	24/09/2014 17:00:00	18.00
3	19/09/2014 23:00:00	22/09/2014 16:00:00	6.00
4	22/09/2014 15:00:00	22/09/2014 16:00:00	1.00
5	22/09/2014 16:00:00	22/09/2014 18:00:00	2.00
Sheet1

Cell Formulas
Range	Formula
C2	=IF(INT(A2)=INT(B2),(B2-A2)*24-IF(AND(MOD(A2,1)*24<=3,MOD(B2,1)*24>=14),11,0),IF(NETWORKDAYS(A2,B2)<=2,0,NETWORKDAYS(A2,B2)-2)*13+("24:00"-MOD(A2,1))*24-IF(MOD(A2,1)*24<=3,11,0)+(MOD(B2,1))*24-IF(MOD(B2,1)*24>=14,11,0))

 

[barry houdini]

This formula should work OK, even if the start/end times are outside your working hours, e.g. 9/27/2014 02:00 to 9/27/2014 07:00 will give you the result 1:00 (one hour)

=(NETWORKDAYS(A2-"3:00",B2-"3:00")-1)*MOD("3:00"-"14:00",1)+IF(NETWORKDAYS(B2-"3:00",B2-"3:00"),MAX(MOD(B2-"3:00",1),"11:00"),1)-MAX(NETWORKDAYS(A2-"3:00",A2-"3:00")*MOD(A2-"3:00",1),"11:00")

Format result cell as [h]:mm

If you prefer to have the hours in decimal format like 27.5 rather than 27:30 then multiply the whole formula by 24 and format result cell as number

 

[skorpionkz]

sorry figure out where is the difference. nice formula

 

[barry houdini]

Thanks Andrzej,

I think your last suggestion fails sometimes if the end time is Saturday morning, e.g. 25/09/2014 22:00 to 27/09/2014 01:00 gives me 3 rather than 16 as expected.

If A2 and B2 will always be within the working hours then this formula should be sufficient

=(NETWORKDAYS(A2-"3:00",B2-"3:00")-1)*"13:00"+MOD(B2-"3:00",1)-MOD(A2-"3:00",1)

format as [h]:mm as before

 

[skorpionkz]

yeah. My formula kind of "moving" 0:00-03:00 on saturday to monday. it works fine as long as there is no start day on monday before 3:00. Your formula handle this better.
Not sure how this works though

NETWORKDAYS(A2-"3:00",B2-"3:00")

is it just ignore all entries before 3:00?

I see your point about last day on saturday... if you will check in my formula, i calculating number of working days -2, to take all whole days and *13.
BUT as saturday is not working day. it actualy ignore 1 whole day... good point

 

[barry houdini]

My normal formula for counting MF hours work hours (assuming start and end times/dates are within working hours), with StartTime and EndTime defining the working day, e.g. StartTime = 09:00 and EndTime = 17:00 is this

=(NETWORKDAYS(A2,B2)-1)*(EndTime-StartTime)+MOD(B2,1)-MOD(A2,1)

....but when the working period goes beyond midnight that doesn't work correctly so my solution is to "offset" all references to A2 and B2 by the amount the working day goes past midnight (hence A2-"3:00" etc. in this case) and then assume the work period is also 3 hours earlier, i.e. 11:00 to midnight in this case, hence the references to "11:00" in my first suggestion.

 

[skorpionkz]

good idea. and you not gonna loose any Monday morning hours as on Monday it starts at 2PM (offset to 11AM). Will need to remember this trick</SPAN>