Adding Business Hours to a date

[egagnon]

Good Day... I'm having a problem figuring out how to add time to a date and exclude non-working hours/holidays/and weekends. If I use this formula "MOD(D26,1)-MOD(B26,1)+(NETWORKDAYS(B26,D26)-1)*3/8" for example if my inputs are 03/18/2005 2:00 PM and 03/21/2005 9:00 AM it returns a time of 4 hours, which is correct. My problem is that I need my inputs to be the first date and the number of hours and a formula that returns the later date. Any help would be appreciated....

 

[fairwinds]

Yes, and I've raised the issue twice.

You say that we should calculate 8 hours per day

but

working hours 8 am - 5 pm, without lunch = 9 hours


That inconsistancy will get you one hour off at some occasions - you found one.

 

[egagnon]

I'm sorry, I must not have understood. I need to calculate on a nine hour day. Will changing the division from a 9 to an 8 fix the problem, or is there more?

 

[fairwinds]

Yes.

=IF(17-MOD(A2,1)*24>B2,A2+B2/24,WORKDAY(A2,MAX(1,INT((B2-(17-MOD(A2,1)*24))/9)))+"08:00"+MOD((B2-(17-MOD(A2,1)*24)),9)/24)

should do it.

 

[egagnon]

That fixed some of the problem, but also, when the time is wrapping around two days, it is not working correctly. When it should go on to the next day, it will instead start over at 8:00 am. For example, if i put 03/21/05 2:30 PM it will report correct up to 11 hours, which will be 03/22/05 4:30 PM, but when I put 12 hours it should report 03/23/05 8:30 AM, but instead it reports this as 8:30 AM on the 22nd. The same thing happens as I increase the time to 3:30 in the example, it will not wrap to the next day for 11 hours. It will not go two days unless you count all they way through the clock again and then it will report on the 23rd. Do you know how to fix this?

 

[fairwinds]

Hmm

Really too tired now but try:

=IF(17-MOD(A2,1)*24>B2,A2+B2/24,WORKDAY(A2,1+INT((B2-(17-MOD(A2,1)*24))/9))+"08:00"+MOD((B2-(17-MOD(A2,1)*24)),9)/24)


I'll take a second look at it tomorrow.

 

[egagnon]

I tested this in the tables I had and it fixed it.... thanks so much again.....