Check date is is a range and return value of period

[JonRowland]

Hi,

I would have previously done this use and IF and AND function as only had 2 or 3 periods to check but I now have a lot. So here is my question.
I have a long list and what to find what period the date falls in against in a list containing Start and End dates and then return the value if date falls in that range.

Worksheet One
I have in Col U a date as dd/mm/yyyy hh:mm:ss. I want to check this against worksheet 2 and if falls in the date range return the value in Col C, similar to a X or V LOOKUP

So if U2 is greater or equal to Worksheet2.A1 AND less than or equal to Worksheet 2.B1 value WORKSHEET2.C1 is returned

For example

01:01:23 01/02/2022	P1
23:59:01 02/01/2022	No
11:11:01 07/01/2022	P2

Worksheet Two

00:01:00 01/02/2022	23:59:00 02/01/2022	P1
23:10:40 06/01/2022	11:11:01 07/01/2022	P2
13:22:01 08/02/2022	16:00 09/02/2022	P3

All ranges are dynamic.

I'd prefer a formula rather than VBA but not discounting VBA. I did think I could write a loop but would have to run through each series individually (I think).

Any guidance as always greatly received and hope I'm understandable.

 

[Legacy 143009]

I think you can achieve this with XLOOKUP in office 365 but I am not familiar with it. Here is the old school method:

=INDEX(Sheet2!$C:$C,MATCH(TRUE,(Sheet2!$A:$A<=$U2)*(Sheet2!$B:$B>=$U2),0))

 

[JonRowland]

I think you can achieve this with XLOOKUP in office 365 but I am not familiar with it. Here is the old school method:

=INDEX(Sheet2!$C:$C,MATCH(TRUE,(Sheet2!$A:$A<=$U2)*(Sheet2!$B:$B>=$U2),0))

This ends up giving me a #N/A value when it should return a value. Stump as never been able to master Index/Match

 

[Legacy 143009]

Try:

=INDEX(Sheet2!$C:$C,MATCH(1,(Sheet2!$A:$A<=$U2)*(Sheet2!$B:$B>=$U2),0))

 

[JonRowland]

Try:

=INDEX(Sheet2!$C:$C,MATCH(1,(Sheet2!$A:$A<=$U2)*(Sheet2!$B:$B>=$U2),0))

That works. Fantastic. Thank you Flashbond.

Strangely enough, I decided to write some rushed VBA. Good thing is that each method returned the same results. Needs a little improving but does the job. [I'm in between PCs as my current one is slowly dying on me hence the rush before another BSOD)

Sub BtwDates()

Dim Row As Long
Dim Trow As Long

Dim StartRow As Long
Dim LastRow As Long

Dim TStartRow As Long
Dim TLastRow As Long

Dim DateVlu As Date
Dim StPeriod As Date
Dim EndPeriod As Date
Dim DatePeriord As String

Call TurnoffAlerts ' My proc for turning of screen updating etc

StartRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

' T is the date period list
TStartRow = 2
TLastRow = ActiveWorkbook.Sheets("Trips").Cells(Rows.Count, "A").End(xlUp).Row

' Loop within a loop.
For Trow = 2 To TLastRow

' Places the values of Start & End Date and the Period referred into variables 
StPeriod = ActiveWorkbook.Sheets("Sheet1").Cells(Trow,1)
EndPeriod = ActiveWorkbook.Sheets("Sheet2").Cells(Trow,2)
DatePeriod = ActiveWorkbook.Sheets("Sheet2").Cells(Trow, 3)

  For Row = StartRow To LastRow

DateVlu = Cells(Row, 13) ' When date stored

If DateVlu >= StPeriod And DateVlu <= EndPeriod Then

Cells(Row, 12) = DatePeriod 'Update main WkSht with DatePeriod Value
End If
    Next Row
    
    Next Trow
    
    Call TurnonAlerts ' Turn updating back on
End Sub

 

[Legacy 143009]

My VBA approach would be:

Sub myFunction()
  Dim s1lRow As Long, s2lRow As Long
  Dim sh1 As Worksheet
 
  Set sh1 = Worksheets("Sheet1")
  s1lRow = sh1.Cells(Rows.Count, 21).End(xlUp).Row
  s2lRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

  Call TurnoffAlerts
  With Worksheets("Sheet2")
    For j = 2 To  s1lRow
      For i = 2 To s2lRow
        If sh1.Cells(j, 21).Value >= .Cells(i, 1).Value And sh1.Cells(j, 21).Value <= .Cells(i, 2).Value Then
          sh1.Cells(j, 22).Value = .Cells(i, 3).Value
        End If
      Next
    Next
  End With
  Call TurnonAlerts
End Sub

 

[JonRowland]

Thanks Flashbond - looks neater than mine. I will give this a try once I am properly up and running again.