PDA

View Full Version : [SOLVED] Match Overlap Date Range and multiple criterias



JOEYSCLEE
05-23-2018, 03:01 AM
Hi, there

I have 2 tabs - Holiday and Result in the attachment.

On holiday tab, I state different date range and holiday name for each country. Would you please help to advise how I can get the result - holiday name with the exact date on Result tab which falls into holiday.:help

Paul_Hossler
05-23-2018, 04:23 AM
I'd use a User Defined Function to avoid a complicated worksheet formula




Option Explicit

Function GetHoliday(Cntry As String, Date1 As Date, Dates As Range) As String
Dim vDates As Variant
Dim i As Long
Dim s As String

vDates = Intersect(Dates.Parent.UsedRange, Dates).Value
s = UCase(Cntry)


For i = LBound(vDates, 1) + 1 To UBound(vDates, 1)
vDates(i, 1) = UCase(vDates(i, 1))
If vDates(i, 1) = s Then
If vDates(i, 3) <= Date1 And Date1 <= vDates(i, 4) Then
GetHoliday = vDates(i, 2)
Exit Function
End If
End If
Next i

GetHoliday = "Not Holiday"
End Function

JOEYSCLEE
05-23-2018, 08:33 PM
Thanks Paul! It's workable:yes