VBA- Check if date cells in column are between 17:00 from previous day to 17:00 current day and clear contents if they are not in between the time

[Indominus]

Hi, how would I clear the cell contents which are formatted as dates in a column if the dates do not fall in between 17:00 from yesterday to 17:00 on the current day? Using VBA. Thank you in advance!

 

[PeteWright]

Insert this code into your workbook as module, select desired cells and run it as a Macro (Ribbon Menu > Developer > Macros)

Sub clearDates()
   Dim ws As Worksheet
   Dim sel As Range
   Dim cel As Range
   Dim currDate As Date
   Dim prevDate As Date
   
   Dim curr As Date
   Dim prev As Date
   
   currDate = Date
   prevDate = currDate - 1
   curr = DateValue(currDate) + TimeValue("17:00:00")
   prev = DateValue(prevDate) + TimeValue("17:00:00")
   
   Set ws = ActiveSheet
   Set sel = Selection
   
   For Each cel In sel
      If IsDate(cel) Then
         If (cel.Value2 < prev Or cel.Value2 > curr) Then
            cel.ClearContents
         End If
      End If
   Next
   
End Sub

 

[Indominus]

Thank you so much this worked perfectly!