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]

Welcome to the board!


If you only need the dates:

=WORKDAY(A1,ROUNDUP(B1/8,0))

A1 = start date
B1= hours


If you need date and time you need to specify your office hours.

 

[egagnon]

Yes, I do need date and time and office hours are 8:00 AM to 5:00 PM Monday through Friday. For example... I need it so if I enter 03/18/05 2:00 PM in A1 and 5 hours in A2 the formula in A3 will report 03/21/05 9:00 AM. Also, I know that I can input holidays using the NETWORKDAYS funcion. WIll this also work with WORKDAY function as well?

 

[fairwinds]

8 to5 is 9 hours. Do we need to consider lunch break too?

 

[egagnon]

Nope, no lunch break considered...

 

[fairwinds]

Please explain what answer you want for the following example and how you would want it to be calculated.

Book1
	A	B	C	D
1	Start	Hours
2	2005-03-18 10:00 AM	10
3
Sheet4

 

[egagnon]

If I had those two inputs I would want the equation to out put 03/21/2005 11:00 AM. Is this possible?

 

[fairwinds]

Try:

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


You might find some results odd due to the fact that we calculate 8 hours per day but the work time you specify (8 to 17) is 9 hours. You can adjust that if you want.

A holliday range can be added like this:


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

 

[egagnon]

Hey... this works great...thanks a lot

 

[egagnon]

I've run into a problem with this formula. when I put 03/21/05 at 2:30 pm as the received date and then 10 hours... it will give the correct output of 03/22/05 at 3:30 pm, but if I increase it to 11 hours instead of giving me the correct 03/22/05 at 4:30, it responds with 03/22/05 at 8:30 am. Do you know why this would be happening?