excelvbadatetime-format

VBA code to format time within a select case

I have set the custom number format to YYYYMMDD HH:MM:SS for two columns but its not working with my VBA code. Can someone please help me with my code to format it using VBA instead.

For i = startRow To lastRow
    Select Case Range("O" & i).Value
        Case "A", "C", "D", "E", "L", "AI", "CI", "DI", "EI", "LI", "AP", "CP", "DP", "EP", "LP"
            Range("N" & i).Value = "19000101" & " " & Range("L" & i).Value
        Case "B", "BI", "BP", "N", "NI", "NP"
            Range("N" & i).Value = "19000102" & " " & Range("L" & i).Value
        Case Else
            Range("N" & i).Value = ""
    End Select
    
Next i

Thanks in advance :)

Sheet1

Solution

  • You need to use something like this;

        Range("N" & i) = "19000101 " & Format(Range("L" & i), "hh:mm")