ExcelBanter - View Single Post - Tricky Date calculation: How to calculate a future date

daddylonglegs

Wrote:
I have done a lot of research using these groups but just can't get a
solution to what I'm after: I would like to calculate a future (EndDT)
date based on the following given input: StartDT & Time; DayStart;
DayEnd; HolidayList; OffSet (in hh:mm for the future date
calculation).
What is very important is that the calculated future date must be a
date and time and the calculation must only use weekdays and business
hours for the future date calculation (no weekends, no holidays).
Somehow I cannot string together the correct logic using NETWORKDAYS
(and some other crude ways I'd rather not mention!). I did find a
number of very good examples but (most) are based around a given start
& end date. If anyone can help, please advise. Many thanks in advance,
Chris.

Hi Chris, have you found an answer yet?

Assuming your startDT & Time in one cell - A2 - and that this day will
be a workday - and time will be within your business hours (i.e.
between DayStart and DayEnd)

DayStart in B2
DayEnd in C2
Offset is the total number of hours you wish to add, e.g. 76:43 - in D2
(formatted [h]:mm)
Holiday list is G1:G10

then use the following formula, with result cell formatted
appropriately e.g. mmm-d-yyyy hh:mm

=WORKDAY(A2,(D2-B2+MOD(A2,1))/(C2-B2),G$1:G$10)+B2+MOD(D2-B2+MOD(A2,1),C2-B2)

e.g. if you have the following

StartDt & time Aug-8-2006 15:23
DayStart 08:00
DayEnd 17:00
Offset 25:11
Holiday in G1 Aug-10-2006

Result of the above formula

Aug-14-2006 13:34

--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=569326