VBA Check if Date:Time Value is Between Other Date:Time Values

[tryingmybest418]

Hi all,

I am trying to write a macro that checks if a timestamp from an event falls between a series of two other time stamps.

There is a timestamp in cell B2 and if it falls between the timestamps in G2 & H2, or G3 & H3, or G4 & H4 etc... I would like A2 to display "ERROR"
The macro can stop running if any of the ranges throw "ERROR".

It would ideally check something along the lines of:

IF (date/time) in B2 is greater than (date/time) in G2 and less than (date/time) in H2, then A2 = "ERROR"
IF NO ERROR THEN
IF (date/time) in B2 is greater than (date/time) in G3 and less than (date/time) in H3, then A2 = "ERROR"
IF NO ERROR THEN
IF (date/time) in B2 is greater than (date/time) in G4 and less than (date/time) in H4, then A2 = "ERROR"

And run until there is no value in column H.


All of the date/time values are in the format:
08/24/2018 10:17:10 AM


Thanks in advance!

 

[Peter_SSs]

See if this does what you want. If so, do you really need a macro? A formula in A2 could do the same job.

Sub CheckTimes()
  Dim lr As Long
  
  lr = Range("H" & Rows.Count).End(xlUp).Row
  Range("A2").Value = Evaluate("if(countifs(G2:G" & lr & ",""<""&B2,H2:H" & lr & ","">""&B2),""ERROR"","""")")
End Sub

 

[tryingmybest418]

Thanks for the help, Peter!
I tried a few things based off of your suggestion and got something working here.

You're right, if it was a standalone issue, I would have used a function in the cell. But the cell may be moving and there are a few other variables, so I trimmed the question down a bit to make it easier to explain and then adjust as needed after the fact.

Thanks again!

 

[Peter_SSs]

OK, glad you have it sorted. Thanks for the update.