Calculating time additions excluding non business hours.

[Matthew Bruce]

I need to create a "Calculator", which takes a date/time entered by the user, and adds a certain time (up to 100hours) based on criteria, giving a another date/time. To complicate matters, I need to exclude non business hours and weekends for some criteria, but not others.

Example

If Criteria 1=Green, Criteria 2 = 1 or 2 or 3
If 1, + 08:00 (24/7)
If Criteria 1 = Red, Criteria 2 = A or B or C
If A, +08:00 exluded non business hours (17:30 - 09:00) and weekends


User Entry....................... Criteria 1 ..................Criteria 2 ..........Calculated Time
12/03/14 12:00................ Red .........................A ......................= 13/03/14 11:30
12/03/14 12:00................ Green ......................1 ......................= 12/03/14 20:00



All I have so far is a Networkdays formula that calculates the time between two dates excluding 17:30-9:00 and weekends.

=(NETWORKDAYS(A4, C4)-1)*("17:30"-"09:00")+IF(NETWORKDAYS(C4,C4),MEDIAN(MOD(C4,1),"17:30","09:00"),"17:00")-MEDIAN(NETWORKDAYS(A4,A4)*MOD(A4,1),"17:30","09:00")

I don't mind if its complicated or long winded, just need it to work. VBA is probably beyond me but will give it a go.

Thanks.

 

[kvsrinivasamurthy]

Try this

=IF(B1="Green",A1+"8:00",IF(B1="Red",IF(MOD(A1,1)+"8:00">TIMEVALUE("17:30"),WORKDAY(A1,1)+"9:00"+"8:00"-"17:30"+MOD(A1,1),A1+"8:00"),""))

 

[Matthew Bruce]

This almost works, but doesn't account for when the time entered is already outside business hours.

 

[kvsrinivasamurthy]

Try this modified formula,

=IF(B1="Green",A1+"8:00",IF(B1="Red",IF(MOD(A1,1)+"8:00">TIMEVALUE("17:30"),WORKDAY(A1,1)+"9:00"+"8:00"-MAX("17:30"-MOD(A1,1),"0:00"),A1+"8:00"),""))

 

[Matthew Bruce]

Oh, closer!

However this does not work when the time added goes beyond the first business day, so more than 8.5 hours......

 

[Matthew Bruce]

Find the solution!

=IF($B$7=0,(WORKDAY(A10-1,CEILING((B6+MEDIAN(NETWORKDAYS(A10,A10)*MOD(A10,1),B$2,C$2)-B$2)/(C$2-B$2),1))+MEDIAN(NETWORKDAYS(A10,A10)*MOD(A10,1),B$2,C$2)+B6-CEILING(MEDIAN(NETWORKDAYS(A10,A10)*MOD(A10,1),B$2,C$2)+B6-B$2,C$2-B$2)+C$2-B$2),A10+B6)

This adds a time (b6) to a date/time (a10), and returns a date/time. Depending on B7, it either adds it directly, or adds the time assuming only a 8.5 hour day between 09:00 (b2) and 17:30 (c2) and just weekdays.

It can cope with any date/time entered, not just those within the 8.5 hour window.
Awesome.

 

[Kristof Goossens]

Hello,

could someone further change this formula so that it possible to include weekends or not. I work in a production company where the working hours in production can change from just two shifts during the week to continuous production.

So to build a production estimation time table I need to able to switch on/off:
working between 6.00 and 21.00 or the whole day (that's okay)
working weekends or not
This makes 4 (or even more) possible schedules
2 shifts during week only
2 shifts week + weekend
3 shifts during week only
3 shifts week + weekend
+ if possible 3 shifts during the week + 2 shifts in the weekend

I have quite ok basics in Excel (indirect - address, index match, isna etc.), but this is way over my head.

If anyone could help me with this or just even try to explain how to understand the formula created by Matthex (why median, mod, ...) was build up, thanks in advance!