• Status: Solved
• Priority: Medium
• Security: Public
• Views: 182

# 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.

Massimo
SpecialDays1.JPG
SpecialDays2.JPG
Special-Days.xls
0
Massimo Scola
• 4
• 3
• 2
1 Solution

EngineerCommented:
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
0

Manager - EngineeringCommented:
Hi,

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

KK,
0

Manager - EngineeringCommented:
Hi,

Use this simple formula to get your result

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

KK,
0

Manager - EngineeringCommented:
Hi,

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

KK,
0

EngineerCommented:
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),"")
0

Author Commented:
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?

0

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

EngineerCommented:
Try this sub

Sub holidayswithin()
Dim tws As Worksheet, sws As Worksheet
Dim trw As Range, srw As Range
Dim hstring As String
Set tws = Sheets("Date")
Set sws = Sheets("Special Days")
For Each trw In tws.Range("A2:A" & tws.Range("A2").End(xlDown).Row)
hstring = ""
For Each srw In sws.Range("A2:A" & tws.Range("A2").End(xlDown).Row)
If (trw <= srw And trw.Offset(0, 1) >= srw) _
Or (trw <= srw.Offset(0, 1) And trw.Offset(0, 1) >= srw.Offset(0, 1)) Then
hstring = hstring & srw.Offset(0, 2) & ", "
End If
Next srw
If Len(hstring) > 2 Then hstring = Left(hstring, Len(hstring) - 2)
trw.Offset(0, 3) = hstring
Next trw
End Sub
0

Author Commented:
Thanks a lot. That really helped me!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.