Looping through Dates to Determine in Date Falls Between Other Dates

[Sully1440]

I'm trying to determine if dates are between other dates looping through the sheet using the code below. If the date falls within the other dates, I want to write the word "Yes" on each line. But I'm having trouble getting by the errors.....please help. thx....


Sub Date_Checker()
Dim dtmMyDate As Date
Dim AC_StartDate As Date
Dim AC_FinishDate As Date
Dim Rng As Range
Set Rng = Range("D4", Range("D4").End(xlDown))
Counter = Rng.Count

For i = 4 To Counter

AC_StartDate = Range("E(i)").Value
AC_FinishDate = Range("F(i)").Value

dtmMyDate = (Range("J(i)").Value)
If dtmMyDate > AC_StartDate And dtmMyDate < AC_FinishDate Then
Range("M(i)").Value = "YES"
Else
Range("M(i)").Value = "NO"
End If


If WorksheetFunction.Min(Rng) >= 0 Then Exit For
Next i

End Sub

[Paul_Hossler]

I don't have an idea as to what you're really try to do since you only check rows 4 to 7 in cols J and K

And this isn't the way I'd do it (different personal style), but I think the problem (which you never said what it is) is the way you're referring to cells.
Look at the <<<<<<<<<<<<<<<< line

The Exit For causes only one YES to be written




Option Explicit


Sub Date_Checker()
Dim dtmMyDate As Date
Dim AC_StartDate As Date
Dim AC_FinishDate As Date
Dim Rng As Range
Dim counter As Long, i As Long


Set Rng = Range("D4", Range("D4").End(xlDown))
counter = Rng.Count


For i = 4 To counter


AC_StartDate = Range("E" & i).Value '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
AC_FinishDate = Range("F" & i).Value


dtmMyDate = (Range("J" & i).Value)


If dtmMyDate > AC_StartDate And dtmMyDate < AC_FinishDate Then
Range("M" & i).Value = "YES"
Else
Range("M" & i).Value = "NO"
End If


If WorksheetFunction.Min(Rng) >= 0 Then Exit For


Next i


End Sub






You might want to attach a workbook with the results that you're expecting, and it might be easier to figure something out for you

[Sully1440]

Hi Paul,
Thanks for getting back to me. I'm trying to do the following:
- check if any of the 2nd table dates/times (the entire table of lines), fall within the dates/times of the 1st table
- for example: for line 3 (cell B3:D3), check the 2nd table to see if the dates in this table fall within it (or vice versa)
- If yes, place "Yes" in Column E for each line.

I placed the actual data in the attached sheet.
Any help would be appreciated.
Thanks,
Jim

[Paul_Hossler]

So ...


Check each of the 110 Start/Finish in col CD

against each of the 325 X/Y in H:I and

if the DE Start/Finish is entirely contained in any X/Y, then

put a YES by that line in E and

stop checking that Start/Finish and

Get the next Start/Finish

IS that it?

[Paul_Hossler]

Option Explicit




Sub Date_Checker()
Dim aryDE As Variant, aryLA As Variant
Dim i As Long, j As Long

Application.ScreenUpdating = False

With Worksheets("Date Checker")
aryDE = .Range("B1").CurrentRegion.Value
aryLA = .Range("H1").CurrentRegion.Value


For i = LBound(aryDE) + 1 To UBound(aryDE)
For j = LBound(aryLA) + 1 To UBound(aryLA)
If aryLA(j, 2) <= aryDE(i, 2) And aryDE(i, 3) <= aryLA(j, 3) Then
.Cells(i, 5).Value = "YES"
Exit For
End If
Next j
Next i
End With


Application.ScreenUpdating = True




MsgBox "Done"


End Sub

[Sully1440]

Hey Paul,
Awesome :) This is exactly what I asked for. I forgot to add one small piece to it (my bad).

First Ask (COMPLETE Thank you): Check each of the 110 Start/Finish in col CD, against each of the 325 X/Y in H:I and, if the DE Start/Finish is entirely contained in any X/Y, then put a YES by that line in E and stop checking that Start/Finish and Get the next Start/Finish

Second Ask:
1.) Can it be setup such that it only does the search/comparison for the corresponding AC number and then if found moves on to the next one? For example: In the first instance, only look for AC #101 (1st record) in the other table to see if the date is entirely contained as you've already done. Once it goes through the records corresponding to the 1st matching record, move on to the next one.
2.) Can you also help me with .......if the DE Start/Finish is PARTIALLY contained in any X/Y, then put a PARTIAL by that line in F and stop checking that Start/Finish and Get the next Start/Finish. To be honest, I'm not even sure if there is a date that starts within the other range but extends beyond the finish date. (This might be confusing to explain. I hope I explained it correctly).

Thx,
Jim

[Paul_Hossler]

Think this is it

I added AC 999 test line for PARTIAL check



Option Explicit

Sub Date_Checker()
Dim aryDE As Variant, aryLA As Variant
Dim i As Long, j As Long

Application.ScreenUpdating = False

With Worksheets("Date Checker")
aryDE = .Range("B1").CurrentRegion.Value
aryLA = .Range("H1").CurrentRegion.Value


For i = LBound(aryDE) + 1 To UBound(aryDE)
For j = LBound(aryLA) + 1 To UBound(aryLA)

If aryDE(i, 1) = aryLA(j, 1) Then ' AC match
If aryLA(j, 2) <= aryDE(i, 2) And aryDE(i, 3) <= aryLA(j, 3) Then 'DE start and end within LA start and end
.Cells(i, 5).Value = "YES"
Exit For

ElseIf aryLA(j, 2) <= aryDE(i, 2) And aryDE(i, 2) <= aryLA(j, 3) Then ' DE start after LA start and before LA end
.Cells(i, 6).Value = "PARTIAL"
Exit For
End If
End If
Next j
Next i
End With


Application.ScreenUpdating = True


MsgBox "Done"


End Sub

[Sully1440]

:)
This is it. Thank you Paul. I appreciate your help. This date checker model will help us work through our data set.
I need to learn how to work with arrays (lol).

Jim

[Paul_Hossler]

:)

I need to learn how to work with arrays (lol).

Jim


Good reference

https://www.snb-vba.eu/VBA_Arrays_en.html

[snb]

Or


Sub M_snb()
sn = Sheet1.Range("H1").CurrentRegion.Offset(, -6).Resize(, 9)

For j = 3 To UBound(sn)
If sn(j, 2) = "" Then Exit For
For jj = 3 To UBound(sn)

Select Case (sn(j, 2) > sn(jj, 8)) * (sn(j, 2) < sn(jj, 9)) & (sn(j, 3) > sn(jj, 8)) * (sn(j, 3) < sn(jj, 9))
Case "11"
sn(j, 4) = "YES"
Case "10", "01"
sn(j, 5) = "Partial"
End Select

Next
Next

Sheet1.Range("H1").CurrentRegion.Offset(, -6).Resize(, 9) = sn
End Sub