Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Tricky Date calculation: How to calculate a future date

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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default Tricky Date calculation: How to calculate a future date

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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Tricky Date calculation: How to calculate a future date


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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Tricky Date calculation: How to calculate a future date


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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Tricky Date calculation: How to calculate a future date


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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Tricky Date calculation: How to calculate a future date


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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Tricky Date calculation: How to calculate a future date


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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Tricky Date calculation: How to calculate a future date


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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Tricky Date calculation: How to calculate a future date

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


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Tricky Date calculation: How to calculate a future date

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
A simple date calculation question. Seldum Excel Discussion (Misc queries) 11 June 29th 06 10:48 AM
A simple date calculation question. Seldum Excel Discussion (Misc queries) 2 June 26th 06 04:56 PM
How to return a value between date ranges Mary-Lou Excel Worksheet Functions 7 May 26th 06 10:00 PM
Auto calculate for date + days forward to yield new date John Sullivan Excel Worksheet Functions 1 April 22nd 06 05:18 PM
Formula to determine a future date based on criteria David Excel Worksheet Functions 2 December 15th 04 08:51 PM


All times are GMT +1. The time now is 02:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"