Need a formula...Date & time related...bit Complex...need help...!!!

[naven_sg]

Hello All,

I need to add 29 Business hours to a Date and Time. Business hours is 7:00 AM to 19:00 PM.

Currently I am using the following formula (which i got in one of the thread):

A1: 8/26/2011 7:00:00 AM
B1: 1/1/1900 5:00:00 AM

D2: 7:00:00 AM
E2: 7:00:00 PM

Formula: =WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2

answer i am getting : 8/30/2011 12:00 (adding 29 hours to A1)

If A1 is within Business hours, the result which i am getting is correct. However the problem arises when the Date and Time entered in A1 is Out side working hours or the Date is Sat (or) Sunday.

In the same example if A1 is outside working hours say "8/26/2011 6:00:00 AM"...then with this formula i am getting "8/30/2011 11:00" which is wrong...i should actually be getting "8/30/2011 12:00".

The same applies if i have the Date and Time after 19:00, calculation should start from next working day at 7:00 AM.

Also with respect to Sat & Sunday, if i have the Date time any time in Sat (or) Sunday...it should actually start from next working day (i.e) Monday 7:00 AM.

Can you please help to incorporating the above 2 conditions to the formula which i mentioned earlier.

Thanks in Advance.