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.

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
|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.
|

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

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

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

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

daddylonglegs wrote:
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
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

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


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

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,
Chris
daddylonglegs wrote:
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


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

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!

wrote:
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,
Chris
daddylonglegs wrote:
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


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