Trying to Calculate a Projected Date

[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

 

[Norman Harker]

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.

 

[jedicris]

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

 

[Norman Harker]

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.

 

[jedicris]

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

 

[Guest]

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.

 

[jedicris]

Hi,

Thanks for that.

I think Norman deserves some rest

 

[jedicris]

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

 

[Norman Harker]

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.