Trying to Calculate a Projected Date

  • Thread starter Thread starter jedicris
  • Start date Start date
J

jedicris

I'm trying to create a date calculator that takes account of holidays.
i.e.
Start date = 13/09/04
Finish date = 13/09/04 + 15 weeks but makes allowances for holidays.

The formulae used is shown below. I have created a list of holidays,
which is the named range holidays.

Start Date 38243
No of Weeks 15
Days to add =(No_of_Weeks-1)*5
Finish Date =WORKDAY(Start_Date,C4,holidays)

The results are shown below:

Start Date 13/09/2004 Monday
No of Weeks 15
Days to add 70
Finish Date 13/01/2005 Thursday

The finish day should be the 17/01/2005 and the same day as the start
day, however it drops a few days.

This worked well until I inserted the holidays list.

Does anybody have any idea what is going wrong.

I have attached a copy of the spreadsheet to give a better idea.

I'm using excel 2002 on windows XP pro.

Thanks to anybody who can help me.

Attachment filename: date projector.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=455788
 
Hi jedicris!

The function appears to be working correctly.

If I just add the number of workdays excluding your holiday range

=WORKDAY("13-Sep-2004",70)
Returns: 20-Dec-2004

I now adjust for 18 days of holidays:

=WORKDAY("20-Dec-2004",18)
Returns: 13-Jan-2005

Which is the same as:
=WORKDAY(Start_Date,C4,holidays)

I've also done it manually as well and this confirms 13-Jan-2005


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi,

Thanks for looking at this. Yes the function does seem to work bu
does not give me the answer I need.

To explain.
I have courses starting on different days of the week and running fo
different numbers of weeks.
So if a course starts on the Monday 13/9/04 and runs for 15 weeks th
last day of the course should be Monday 17/1/05. This is the 15 week
of the course plus 4 mondays which are holidays. Whereas the functio
returns a finish date of 13/1/05, the previous Thursday.

I need to get it to give me a finish date which is the same day as th
start date.

Hope this makes sense
 
Hi jedicris!

It looks to me like you want:

=WORKDAY(Start_Date,15*7,Holidays)

I think that the problem with the earlier method was playing around
yourself with working days. Incidentally, there's no need to worry if
a holiday falls on a Saturday or Sunday because the function only
excludes it once.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

Thanks again for that.

Unfortunatly, it gives me the same result, a thursday.

The function works OK until it has to roll over to the next year.

From a start date of 13/09/04 I have tried various week numbers and al
is well until I get to 13 weeks, which takes it into 2005, it is the
that it goes haywire.

Just a bit more explanation. The courses are only for one day pe
week. So a 15 week course = 15 days, a 20 week course =20 days.
Hence, if a course starts on a monday the last day has to be a monday
if it starts on a friday the last day has to be a friday.

Thanks again
 
Hi jedicris,
Norman posted another reply that got seperated from this thread. Thought I would copy it down in case you didn't see it. I'd imagine Norm is enjoying some REM right now.

Regards,
Mark Graesser

***** Copied Message - Start ********
Hi!

Doh! Must get to bed!

=WORKDAY(Start_Date,15*5,Holidays)

It's a 75 day course net of holidays and weekends.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)

***** Copied Message - End ********

----- Norman Harker wrote: -----

Hi jedicris!

It looks to me like you want:

=WORKDAY(Start_Date,15*7,Holidays)

I think that the problem with the earlier method was playing around
yourself with working days. Incidentally, there's no need to worry if
a holiday falls on a Saturday or Sunday because the function only
excludes it once.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Mark,

Many thanks for all your hard work on this.

I have also emailed you directly.

Many thanks again. This will save me many hours of work manuall
calculating finish dates for a couple of hundred courses
 
Hi jedicris!

Re:"Just a bit more explanation. The courses are only for one day per
week. So a 15 week course = 15 days, a 20 week course =20 days.
Hence, if a course starts on a monday the last day has to be a monday,
if it starts on a friday the last day has to be a friday."

Just that little bit of extra detail on requirements makes all the
difference!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top