Excel Formula for Timeline Dates

[beadedbytes]

I need to create a timeline with dates. How would I write a formula that would return workday, but not Sat or Sun or holiday?

Start date 4/9/2009
Start minus 45 2/23/2009 ok
Start minus 70 1/29/2009 ok
Start minus 82 1/17/2009 Sat
Start minus 105 12/25/2008 Holiday

 

[SkipVought]

Hi,

Which holidays? You must enumerate.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[beadedbytes]

For this exercise, let's use some of the traditional holidays -- Xmas Eve, Xmas, New Year's. Depending on future needs, other holidays may be added.

 

[SkipVought]

You will have to enumerate these DATES in a list. My list is named Holidays
[tt]
=IF(MOD(IF(ISNA(MATCH(A11,Holidays,0)),0,1)+A11,7)<2,2-MOD(IF(ISNA(MATCH(A11,Holidays,0)),0,1)+A11,7),0)+IF(ISNA(MATCH(A11,Holidays,0)),0,1)+A11
[tt]
where A11 is the date you want to calculate to the next non-holiday weekday.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I'm realizing that a list of valid dates is the only way to go.

I have worked at several large corporations, and in each case, workdates were not calculated. They are looked up in a table.

The rub comes when holidays are consecutive. I suppose that you could add a column to the lookup table for days to add.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

revised
[tt]
=IF(MOD(IF(ISNA(MATCH(A11,Holidays,0)),0,INDEX(DaysH,MATCH(A11,Holidays,0),1))+A11,7)<2,2-MOD(IF(ISNA(MATCH(A11,Holidays,0)),0,INDEX(DaysH,MATCH(A11,Holidays,0),1))+A11,7),0)+IF(ISNA(MATCH(A11,Holidays,0)),0,INDEX(DaysH,MATCH(A11,Holidays,0),1))+A11
[/tt]
where the table is like...
[tt]
Holidays DaysH
12/25/2008 1
01/01/2009 1
12/24/2009 2
12/25/2009 1
01/01/2010 1
12/24/2010 2
12/25/2010 1
[tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[beadedbytes]

Thanks, Skip. But, I must admit the formula feels a bit daunting.

I remember long ago using the Excel's WORKDAY function, but have not been able to get it to work.

 

[SkipVought]

Did you read the HELP on WORKDAY?

My formula is based on ...

1. the fact that Sat & Sun dates, the MOD/7 is 0 & 1 respectively. So
[tt]
IF(MOD(YourDate,7)<2,2-MOD(YourDate,7),0)
[/tt]
add 2 to saturdays and 1 to sundays

2. the holidays are lookups, and the return the DaysH to add to a date.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, I did not write this hairy formula from scratch, left to right.

I build it, from the inside out, in pieces, like the MOD formula I previously posted.

Start with a concept, test, modify, build.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jgochanour]

I'm having troubles getting the holidays to skip. Skip - mind taking a look at my spreadsheet?

 

[SkipVought]

jgochanour,

Please start a new thread, and explain your problem clearly, concisely and completely.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}