VBA Reformat Exact Date / Exact Time to Date

[Bench]

I've written a routine that deletes rows with either today or yesterdays date in column D or E. However the source data that i have comes in dd/mm/yyyy hh:mm:ss format, with such exact formats the routine doesn't work. this is even after i have changed the format which you can see earlier in the routine.

I've tested it on a seperate sheet manually inputting the dates and it works. How can i replace dd/mm/yyyy hh:mm:ss with something that will work with my routine?

Thanks

Sub Sheet3pm()
    
'   deletes Rows 1:1
    Rows("1:1").Select
    Selection.Delete Shift = xlUp
'    deletes Columns A:A
    Columns("A:A").Select
    Selection.Delete Shift:=xlLeft
    
Range("A:H").Select
    
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("E2") _
        , Order2:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
'   formats D:E to dd-mmm-yy
    Columns("D:E").Select
    Selection.NumberFormat = "d-mmm-yy"
    Cells.Select
'   Deletes Rows Containing Todays Date or Yesterdays
    Last = Cells(Rows.Count, "D").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "D").Value) = (Date) - 1 Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(i, "A").EntireRow.Delete
            
   ElseIf (Cells(i, "D").Value) = Date Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
           Cells(i, "D").EntireRow.Delete
        End If
    Next i
Cells.Select
'   Deletes Rows Containing Todays Date or Yesterdays
    Last = Cells(Rows.Count, "D").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "E").Value) = (Date) - 1 Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(i, "A").EntireRow.Delete
            
   ElseIf (Cells(i, "E").Value) = Date Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
           Cells(i, "A").EntireRow.Delete
        End If
    Next i
    
End Sub

 

[Andrew Poulsom]

Use the Integer portion of the date/time, eg:

If Int(Cells(i, "D").Value) = Date - 1 Then

 

[Bench]

Use the Integer portion of the date/time, eg:

If Int(Cells(i, "D").Value) = Date - 1 Then

Thats great, i'll give it a try shortly, i had come up with quite a long process using "Text to Columns"

SUB QUESTION:

Is there a way that i can make this ignore weekends, ie. if the routine is run on a monday 5/10/09, Date - 1 would apply to Friday 2/10/09 ??

If Int(Cells(i, "D").Value) = Date - 1 Then

Thanks

 

[Andrew Poulsom]

Install the Analysis ToolPak - VBA Add-In and in your project add a reference to atpvbaen.xls. Then you can use:

WorkDay(Date, -1)

in your code.

 

[Bench]

Install the Analysis ToolPak - VBA Add-In and in your project add a reference to atpvbaen.xls. Then you can use:

WorkDay(Date, -1)

in your code.

Thanks, with regard to using int, i get the following:

This is highlighted as a Type Mismatch

If Int(Cells(i, "D").Value) = Workday(Date, -1) Then

Have i missed something here also.

Thanks for your help

 

[Andrew Poulsom]

I wrote:

WorkDay(Date, -1)

not

WorkDay(Date) -1

because the numbr of days must be passed as an argument.