VBA help needed to auto format date column and remove "A" text

[krjoshi]

Folks,

Please help me to auto format the Column "D" and "E" to "DD-MM-YY" format automatically and remove "A" Character. I have attached the excel file for better explains.


Thank you so much in advance

[MarvinP]

Hi Joshi,

You have some Text instead of numbers for your finish date/times. See if the helper columns lets you understand how I fixed these problems.

[krjoshi]

Hi Marvin,
Thanks for your help and advice. Can you please help me to build a macro to replace the formula since the row data will vary frequenlty (then I need to fill down the formula al the time) , and some times both Start and Finish date columns will have "A" text. Please help me to fix these issues.

Thanks,
Joshi

[berlan]

Hi krjoshi,

added an event procedure which automatically formats all new dates that you add, as well as a macro that formats the existing. See the attached and try if it works for you.

In Module1:

Sub FormatDates()

    Dim rng As Range, c As Range
    
    With Sheets("Sheet1")
        Set rng = .Range("A1").CurrentRegion
        Set rng = Application.Intersect(rng, rng.Offset(2), .Columns("D:E"))
    End With

    For Each c In rng
        If Right(c.Value, 2) = " A" Then c.Value = Replace(c.Value, " A", "")
        If c.NumberFormat <> "DD-MM-YY" Then c.NumberFormat = "DD-MM-YY"
    Next c

End Sub

In the Sheet1 module:

Private Sub Worksheet_change(ByVal Target As Range)

    If Application.Intersect(Target, Columns("D:E")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    With Target
        If Right(.Value, 2) = " A" Then .Value = Replace(.Value, " A", "")
        If .NumberFormat <> "DD-MM-YY" Then .NumberFormat = "DD-MM-YY"
    End With
    Application.EnableEvents = True

End Sub

Regards, berlan

[krjoshi]

Hi Berlan and MarvinP, thanks for your time and great help. Thank you so much once again.

Joshi