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

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
0
Massimo Scola
Asked:
Massimo Scola
  • 4
  • 3
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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
 
Kannan KManager - EngineeringCommented:
Hi,

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

KK,
0
 
Kannan KManager - 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Kannan KManager - EngineeringCommented:
Hi,

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

KK,
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Massimo ScolaAuthor 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
 
Saqib Husain, SyedEngineerCommented:
I spotted that later. I think I would prefer VBA to handle that aspect.
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Massimo ScolaAuthor 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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now