asked on

How do I display the name of an occassion between two dates?

Hi

I have two worksheets. In sheet "Date", I have a start date and an end date.
The second worksheet contains a list with all special occassions. There are two special occassions in one week.

As you can see from my screenshots, I'd like Excel to display the name(s) of
the special occassion(s) if it falls between the start and end date.

What is the easiest approach? With a formula or with VBA/custom function?
Thank you for your help. I've attached the spreadsheet to this post.
I appreciate your help.

Massimo
SpecialDays1.JPG
SpecialDays2.JPG
Special-Days.xls

Saqib Husain

Try this formula

=INDEX('Special Days'!$A$1:$C$10,MATCH(1,SIGN((A2<='Special Days'!$A$1:$A$10)*(B2>='Special Days'!$A$1:$A$10)+(A2<='Special Days'!$B$1:$B$10)*(B2>='Special Days'!$B$1:$B$10)),0),3)

THIS IS AN ARRAY FORMULA AND YOU HAVE TO ENTER IT BY PRESSING

CTRL-ALT-ENTER

Kannan K

Hi,

the easiest way is to do thru formula. you can do using IF function.

KK,

Kannan K

Hi,

Use this simple formula to get your result

=IF(A2>='Special Days'!A3, IF(B2<='Special Days'!B3, 'Special Days'!C3,""),"")

KK,

Kannan K

Hi,

sorry, the above formula will work only on the current line. it won't work for range

KK,

Saqib Husain

This will clean up the #NA's

=IFERROR(INDEX('Special Days'!$A$1:$C$10,MATCH(1,SIGN((A2<='Special Days'!$A$1:$A$10)*(B2>='Special Days'!$A$1:$A$10)+(A2<='Special Days'!$B$1:$B$10)*(B2>='Special Days'!$B$1:$B$10)),0),3),"")

Massimo Scola

ASKER

Thanks ssagibh

I tried the formula and it works. There are two holidays in one week: December 7 and December 8
Is it possible to display both?

Saqib Husain

I spotted that later. I think I would prefer VBA to handle that aspect.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Massimo Scola

ASKER

Thanks a lot. That really helped me!