Create a list of Target Dates excluding weekends & holidays 3

[laina222]

Hi!

Is there a good way to create a list of target dates for specific deliverables based on a start date and end date?

For example:

StartDate: 1/1/2007
EndDate: 10/1/2007
Deliverable1: 1/20/2007 --> Should be 1/22/2007 because 1/20/2007 is a Saturday

And so on...

I don't want any deliverable dates to be on a weekend or a holiday. I've taken a look at NETWORKDAYS but our Excel does not have that feature, it seems.

Any bright ideas? Thanks in advance!

 

[SkipVought]

Hi,

You have to go to Tools > Addins and add in the Analysis toolpack to get networkdays.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[GlennUK]

It's Workday that is the function you want. But that is also in the Analysis Toolpak add-in ( menu command Tools/Add-Ins ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Hakala]

This thread almost gives me what I need. What I'm looking for is a way to have Excel give me the closest weekday to a target due date.

My target is 15 days after the original date, but not ending on Saturday or Sunday.

So if I give cell A1 7/2/2007, I want cell A2 to calculate that date, plus fifteen days, and if the result is a weekend, move to the following Monday.

Example:
7/2/2007 would give me 7/17/2007, because it ends on a Tuesday.

7/6/2007 would give me 7/23/2007, because the result of the calculation would be 7/21/2007, a Saturday. The following Monday is 7/23/2007.

Should I start a new thread?



Michelle Hakala

http://www.winebird.com/

 

[Rofeu]

Hi,

I think you're looking for this:

=(A1+15)+IF(WEEKDAY(A1+15,2)>5,8-WEEKDAY(A1+15,2),0)

where A1 is your start date.

Cheers,

Roel

 

[Hakala]

It works! Thank you so much! Have a star.


Michelle Hakala

http://www.winebird.com/

 

[Rofeu]

Quite welcome, thanks for the *

 

[laina222]

Ooooh, that worked for me too - another star!

 

[GlennUK]

A much simpler formula using Workday as I suggested is :

=WORKDAY(A1,15)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Hakala]

Thank you, but the simpler formula doesn't give me the right date. Cell A1 has 7/2. My target date would be 15 days after that, or 7/17. Since 7/17 is not a weekend, that would be correct.

=WORKDAY(A1,15)

gives me 7/23. I believe that's adding 15 workdays to my target date, which is also useful.


Michelle Hakala

http://www.winebird.com/

 

[xlhelp]

Michelle,

I have not been here on the forum for almost a month. I am glad I took a few minutes to visit. That's a very intriguing website. Precise stimulant for people waiting for a nudge. If the spirit moves and cerebral matter co-operates, you might hear from me.

Member- AAAA Association Against Acronym Abusers

 

[yogia]

In addition to Roefu's fine contribution, here is another one ...

=A1+15+CHOOSE(WEEKDAY(A1+15),1,0,0,0,0,0,2)

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[GlennUK]

Thank you, but the simpler formula doesn't give me the right date. Cell A1 has 7/2. My target date would be 15 days after that, or 7/17. Since 7/17 is not a weekend, that would be correct.

=WORKDAY(A1,15)

gives me 7/23. I believe that's adding 15 workdays to my target date, which is also useful.

Oops, my mistake. That's right, it adds 15 workdays to the original. To get the workday after the 15 days has been added on use this:

=WORKDAY(A1+14,1)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Hakala]

That one works! Have a star.


Michelle Hakala

http://www.winebird.com/