Tricky Date calculation: How to calculate a future date

[chriswessels]

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.

 

[Niek Otten]

Use WORKDAY() rather than NETWORKDAYS. You'll have to take care of the time bit yourself

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|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.
|

 

[daddylonglegs]

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]

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]

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]

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

 

[chriswessels]

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

Hi daddylonglegs, I don't know why, but my posts don't seem to appear -
let's try again. Yes, this works very well. I have discovered one
little things that throws it a bit: If the start date and time is
outside of office hours, the formula doesn't cater for starting the
calculation ot the start of the next working day - so using the exact
same variables as before, except that the start date is on Aug-12-2006
15:23, and an offset of 4:00 hours (just to keep it simple) the result
should be Aug-14-2006 12:00.... Can you help with this??
Regards,
Chris

 

[daddylonglegs]

Hi Chris

That makes things a little more complicated, but assuming your StartDT
& Time can be any time, evenings weekends, holidays etc. then you can
use this formula

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

assuming the same setup as previously.

One possible minor problem is that the result would never show the
exact end time of the day but the start time of the next, e.g. given
DayStart of 08:00 and DayEnd of 17:00 with your above example StartDT &
Time of Aug-12-2006 15:23 and offset of 09:00 the result would be
Aug-15-2006 08:00

 

[chriswessels]

Hey daddylonglegs, thanks for your response! This is a whole lot better
than how it was before.... I'm chewing on the impact of the minor
problem within the environment I want to use this (SLA calculations) -
it opens up a little can of little worms Is it not possible to fudge
the formula so that it doesn't roll over into the next day...?

Regards,

 

[chriswessels]

I think I managed to fudge it: I made the offset (9 hours) smaller than
the working hours in the day by increasing the EndDT by 1 second (i.e.
17:00:01). That way a full day remains on the same day instead of
rolling over. From a purist point of view it isn't correct, but the
result I'm seeking won't be skewed by 1 second (but will be by rolling
over to the next day). I'll put it to the test on my data and see if
there is anything else the fudging might affect....! Once again, thanks
for helping me out, I realy do appreciate your time in doing so!