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

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,170
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!
 

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724
Thanks for all of the replies. BobUmlas I tried your suggetion out and it worked great thanks a million.

Best regards,
Charlie
 

coliervile

Well-known Member
Joined
May 19, 2006
Messages
724

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
10,886
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
May 19, 2006
Messages
724
Thank jasonb75 for your solution too. I would like to know why "HolidayListDynamic" did not work in the other formula?

Best regards,
Charlie
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,886
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,437
Members
409,877
Latest member
DDhol
Top