(Solved) – Excel VBA Find and Replace Issue When Changing to Next Day

  • by
(solved)-–-excel-vba-find-and-replace-issue-when-changing-to-next-day

I have this code that works to edit some date formatting issues on archive forms (i am updating the format so this doesn’t happen anymore). It takes the from military time to mmddyyyy 00:00 format. The issue I’m having is overcoming the transition from one day to the next on night shifts form (ex. 12/20/19 23:49 to 12/21/19 01:42) the formatting get weird for excel standard “1/1/1900” then back to “1/0/1900”
code:

Sub FM425_Date()

' FM425_Date Macro
'

'
Dim dt As Variant


Set dt = Range("P3:P4")

Range("G7:G31,I7:I31").Select
Range("I7").Activate
Selection.NumberFormat = "m/d/yy hh:mm;@"
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("P3:P4").Select
Selection.Copy
Cells.Replace What:="1/0/1900", Replacement:=dt, LookAt:=xlPart _
    , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Columns("I:I").ColumnWidth = 18.57
Range("G12:G14,I12:I14").Select
Range("I12").Activate
Application.CutCopyMode = False

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *