Networkdays w/ different hours

[kod2th3e]

I am using the formula below to calculate the elapsed time between a start time and end time with the working hours of 6:30am - 3:30pm and it will rule out weekend days (Saturday/Sunday).

=IF(T2="","",(NETWORKDAYS(S2,T2,)-1)*("15:30"-"6:30")+MOD(T2,1)-MOD(S2,1))

S2 = start time, T2 = end time both fields are in the form of mm/dd/yyyy hh:mm:ss AM/PM

This works great except that on Fridays, I need the time to calculate from 6:30am - 2:30pm because we only work 8 hours on Fridays instead of 9. Any ideas?

 

[Bill_Biggs]

If you can get his attention, Andrew Poulsom can answer this. He is a wiz with these type of questions.

 

[barry houdini]

Try like this

=IF(T2="","",(SUMPRODUCT(INT((WEEKDAY(S2-{2,3,4,5,6})+INT(T2)-INT(S2))/7),{9,9,9,9,8})-IF(WEEKDAY(T2)=6,8,9))/24+MOD(T2,1)-MOD(S2,1))

format result cell as [h]:mm

 

[kod2th3e]

Thanks, that seems to be working great. If I had a list of holidays in cells AK2:AK10 where would I put that in the formula to skip them if they are in between the start and end dates?

 

[barry houdini]

Mmm, that gets a little complicated, especially for just an hour short on a Friday......but this will do that

=SUM((INT((WEEKDAY(S2-{2;3;4;5;6;7})+INT(T2)-INT(S2)-1)/7)-FREQUENCY(IF((AK$2:AK$10>S2)*(AK$2:AK$10< T2),WEEKDAY(AK$2:AK$10,2)),{1,2,3,4,5}))*{9;9;9;9;8;0})/24+MOD(T2,1)-MOD(S2,1)

<?XML:NAMESPACE PREFIX = T2),WEEKDAY(AK$2 /><T2),WEEKDAY(AK$2:AK$10,2)),{1,2,3,4,5}),{9;9;9;9;8;0}) p 24+MOD(T2,1)-MOD(S2,1)<>
This is an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula.

Note: all formulas (including the original one you posted) only work correctly when start date/time and end date/time are both within working hours.....
</T2),WEEKDAY(AK$2:AK$10,2)),{1,2,3,4,5}),{9;9;9;9;8;0})>

 

[kod2th3e]

Barry, thanks so much for your help. I appreciate it a lot!

 

[MoneyPants]

Hello Everyone. I am having a slightly similar problem and I cannot seem to come up with the answer.

Here is the question: My company has business hours between 8am-5pm, and I need to set up a formula that allows me to efficiently track my time on projects. The problem is that I cannot seem to get the formula to match the answer I get when I do it manually. I started a project Thursday, 4/30/2015 at 4:00PM, and ended the project Wednesday, 5/6/2015 at 10:00AM. Also, I took off on the Monday, 5/4/2015, so it is effectively a holiday.

I add this up manually as follows:
Thursday, 4/30/2015 = 1 hour
Friday, 5/1/2015 = 8 hours
Tuesday, 5/5/2015 = 8 hours
Wednesday, 5/6/2015 = 2 hours

It totals 19 hours, but I keep getting 21 hours. Here is my formula: =(NETWORKDAYS(A1,B1,A3)-1)*(B2-A2)+MOD(B1,1)-MOD(B1,1), where
A1= start date
B1= end date
A2= 8:00:00
B2= 17:00:00
A3= 5/4/2015

Does anyone know how I can fix this? Thanks for any help.

 

[barry houdini]

In your manual calculation you are assuming 8 hours each for the full days (Friday and Tuesday), why is that 8 hours if working hours are 08:00 to 17:00? It's either 9 hours per full day or perhaps you are excluding a lunch break?

 

[MoneyPants]

Yes I am assuming a 1 hour lunch break. So if you work from 8:00am-5:00pm, it should only be 8 hours, instead of 9. But I cannot figure out how to capture that in my formula.

 

[barry houdini]

Well, to be entirely accurate you need to know when the lunch break will be (otherwise you can't calculate the hours correctly within the day), so assuming lunch break start in C2 and end in D2 you can use this formula

=(NETWORKDAYS(A1,B1,A3)-1)*(B2-A2+C2-D2)+MOD(B1,1)-MOD(A1,1)+((MOD(B1,1)< C2)-(MOD(A1,1)< C2))*(D2-C2)<c2)-(mod(a1,1)<c2))*(d2-c2)


Assumes that your start and end times will always be within working hours</c2)-(mod(a1,1)<c2))*(d2-c2)