How to get column to fill with Day 1 or Day 2

[coliervile]

Good morning everyone. How can I get column B to fill down with Day 1, Day 2, or Day 3 and so on skipping holiday dates?

Column A1 and B1:

10/1/2014 DAY 1
10/2/2014 DAY 2
10/3/2014 DAY 3
10/6/2014 DAY 4
10/7/2014 DAY 5
10/8/2014 DAY 6
10/9/2014 DAY 7
10/10/2014 DAY 8
10/13/2014 Columbus Day
10/14/2014 DAY 9
10/15/2014 DAY 10
10/16/2014 DAY 11
10/17/2014 DAY 12
10/20/2014 DAY 13
10/21/2014 DAY 14
10/22/2014 DAY 15
10/23/2014 DAY 16
10/24/2014 DAY 17
10/27/2014 DAY 18
10/28/2014 DAY 19
10/29/2014 DAY 20
10/30/2014 DAY 21
10/31/2014 DAY 22
11/3/2014 DAY 23
11/4/2014 DAY 24
11/5/2014 DAY 25
11/6/2014 DAY 26
11/7/2014 DAY 27
11/10/2014 Veterans Day
11/11/2014 DAY 28
11/12/2014 DAY 29
11/13/2014 DAY 30
11/14/2014 DAY 31
11/17/2014 DAY 32
11/18/2014 DAY 33
11/19/2014 DAY 34
11/20/2014 DAY 35
11/21/2014 DAY 36

Best regards,
Charlie

 

[BobUmlas]

Set up a 2-column range with the list of holidays, like
10/13/2014 Columbus Day
11/10/2014 Veterans Day
Name the 1st column "Holidays"
Remove the word "DAY from column B so you only have 1, 2, 3...
Format column B this way:
"DAY "General
Now you'll see DAY 1, DAY 2, etc, but there will only be numbers in the cell.
in B1 put 1
in B2 put this formula in and fill down:
=IF(ISNA(MATCH(A2,Holidays,0)),MAX($B$1:B1)+1,OFFSET(Holidays,MATCH(A2,Holidays,0)-1,1,1,1))

works for me!

 

[jasonb75]

See your original thread, http://www.mrexcel.com/forum/excel-questions/805376-list-workday-holiday-dates.html

 

[coliervile]

Thanks for all of the replies. BobUmlas I tried your suggetion out and it worked great thanks a million.

Best regards,
Charlie

 

[coliervile]

The formula works as it should, but I made a dynamic Defined Name list so that I could add to the list as needed and it did not work. All the formula returns is Day 1, Day 2 and so on without any holidays. Here is the formual I used....
[/code]=IF(ISNA(MATCH(A2,HolidayListDynamic,0)),MAX($B$1:B1)+1,OFFSET(HolidayListDynamic,MATCH(A2,HolidayListDynamic,0)-1,1,1,1))[/code]

Charlie

 

[jasonb75]

It's a bit of an ugly solution, did you try the version that I provided in your original thread?

Adapted to your dynamic range it would be

=IFERROR(VLOOKUP(A2,HolidayListDynamic,2,0),"DAY "&NETWORKDAYS(A$2,A2,INDEX(HolidayListDynamic,0,1)))

 

[coliervile]

Thank jasonb75 for your solution too. I would like to know why "HolidayListDynamic" did not work in the other formula?

Best regards,
Charlie

 

[jasonb75]

If it worked with the fixed range then I'm not sure, my guess would be passing a 2 column range to match was the cause. Maybe Bob will have a better idea of what his formula is doing.