# Short Formula To Pull Upcoming Holidays For a Title Merge In A Huge Database

#### SSElvis

##### New Member
I am Using excel 2007 and running Windows 7 Pro. I have 50 Thousand rows with data.

I am trying to do a merge of different cells to create a title, One of the cells that I need to merge is an upcoming holiday. I have 4 columns of holidays.
For example: Today's date is 5/22/2015 next upcoming holiday would be Memorial Day and after that would be Father's Day.
So, from Today's date to Memorial day the title would contain "Memorial Day" After memorial day, I want the formula to automatically replace "Memorial day" with "Father's Day" And doing the same to upcoming holidays after that.
I have researched forums and gotten ideas to compose a formula. I was able to create one, but the only way I got it to work was with the holiday's date instead of there name. If there is no other way of doing it besides that way I would be okay with it.
Only thing i'm concerned about is Opening, Saving, and Editing a file that large with a formula that big is very time consuming and un-responsive sometimes. I need help and would like to have a formula that dose what I need more efficiently and quicker. ideas on shortening the formula

Here is my formula:=IF(SMALL(IF(\$A\$2:\$A\$7>TODAY(),\$A\$2:\$A\$7),1)=DATE(YEAR(TODAY()),2,14),"Valentine's Day",IF(SMALL(IF(\$A\$2:\$A\$7>TODAY(),\$A\$2:\$A\$7),1)=DATE(YEAR(TODAY()),4,5),"Easter Day",IF(SMALL(IF(\$A\$2:\$A\$7>TODAY(),\$A\$2:\$A\$7),1)=DATE(YEAR(TODAY()),5,10),"Mother's Day",IF(SMALL(IF(\$A\$2:\$A\$7>TODAY(),\$A\$2:\$A\$7),1)=DATE(YEAR(TODAY()),6,21),"Father's Day",IF(SMALL(IF(\$A\$2:\$A\$7>TODAY(),\$A\$2:\$A\$7),1)=DATE(YEAR(TODAY()),10,31),"Halloween",IF(SMALL(IF(\$A\$2:\$A\$7>TODAY(),\$A\$2:\$A\$7),1)=DATE(YEAR(TODAY()),12,25),"Christmas"))))))

I have dates in Array A2:a7
Thanks in advance for any help, tips, and ideas.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
This formula is much shorter. It's an array formula that needs to be confirmed with CTRL-SHIFT-ENTER.

Code:
``=INDEX({"Valentine's Day";"Easter Day";"Mother's Day";"Father's Day";"Halloween";"Christmas",""},1+SUM(--((A2:A7)<=TODAY())))``

After confirming with CTRL-SHIFT-ENTER the formula will be automatically surrounded by curly brackets {}.

Last edited:
I am Using excel 2007 and running Windows 7 Pro. I have 50 Thousand rows with data.

I am trying to do a merge of different cells to create a title, One of the cells that I need to merge is an upcoming holiday. I have 4 columns of holidays.

You want to repeat this formula for 50 thousand rows and return the same result 50 thousand times? If yes, it would be more efficient to calculate the holiday once, and then just repeat the result for the other rows. So if the formula was in B1, put this in B2 =\$B\$1 and copy down column B to B50000.

Last edited:
This formula is much shorter. It's an array formula that needs to be confirmed with CTRL-SHIFT-ENTER.

Code:
``=INDEX({"Valentine's Day";"Easter Day";"Mother's Day";"Father's Day";"Halloween";"Christmas",""},1+SUM(--((A2:A7)<=TODAY())))``

After confirming with CTRL-SHIFT-ENTER the formula will be automatically surrounded by curly brackets {}.

I implemented this formula to my worksheet, unfortunately it wasn't able to update once the dates changed. It always gave me "Valentine's Day", i'm guessing because that one is first on the formula.?

i'm looking for the values to change in between given dates or x amount of days before an upcoming holiday.

Ai, I see I used ";" instead of ","as a delimiter between the holidays (except for the last one, which is ",").
The range A2:A7 in my formula is not fixed, so if you copy the formula you should fix the range (\$A\$2:\$A\$7).
This should be better:

Code:
``=INDEX({"Valentine's Day","Easter Day","Mother's Day","Father's Day","Halloween","Christmas",""},1+SUM(--((\$A\$2:\$A\$7)<=TODAY())))``

Make sure you finish editing the formula with CTRL-SHIFT-ENTER, not just ENTER.

Apologies for my mistakes (here in Europe we use ";" as a delimiter instead of "," so I need to adjust it every time when posting a formula).

Hope this will work for you now.

Replies
3
Views
310
Replies
12
Views
6K
Replies
2
Views
513
Replies
0
Views
1K
Replies
0
Views
645

1,196,264
Messages
6,014,320
Members
441,814
Latest member
youngstubbs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back