Format string to date with double digit day and month using Excel VBA

Question

My worksheet has these formulas. Some of the values in column T are Date and some are string or general:

And its values:

I have this VBA code that loops through each cell in a column and convert the string value to a date and format the date. However, it is working perfectly fine with a double-digit month and day, but not single digits. What is the cause of problem and how can I make it to always result in double digit, i.e "01 - 02 -2021", not "1/2/2021"?

Sub Date_format()

    Dim Cel As Range
    Dim i As Integer
    i = ActiveWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

    ' First I applied mm - dd as the same as the work sheet
    ActiveSheet.Range("T2:T" & i).NumberFormat = "mm-dd-yyyy"

    For Each Cel In ActiveSheet.Range("T2:T" & i)
        If Not Cel.Value = "n/a" Then

            ' Then I applied dd - mm to swap the day and month
            Cel.Value = Format(DateValue(Cel.Value), "dd - mm - yyyy")
        End If
    Next

End Sub

Once I applied .NumberFormat = "dd-mm-yyyy" to the range, my other formula =DAYS(TODAY(),T2) that calculate days, are not working any more on most cells as shown in this picture:

Add `.Range("T2:T" & i).NumberFormat = "dd - mm - yyyy"` before the FOR loop — Siddharth Rout, Dec 12 '21 at 19:15
`once I applied .NumberFormat = "mm-dd-yyyy" to the range my other formula =DAYS(TODAY(),T2) that calculate days are not working anymore on some cells` Which cells? — Siddharth Rout, Dec 13 '21 at 08:09
You mention you have applied `.NumberFormat = "mm-dd-yyyy"` but T5 shows "dd-mm-yyyy"? Can you also update the question with the current code that you are using? — Siddharth Rout, Dec 13 '21 at 08:31
@SiddharthRout first I applied `mm - dd` as same as work sheet. then I re-applied `dd - mm` to swap the day and month. I think the problem is with the date format? — VBAbyMBA, Dec 13 '21 at 12:27
Change `Cel.Value = Format(DateValue(Cel.Value), "dd - mm - yyyy")` to `Cel.Value =DateValue(Cel.Value)`. You have already applied the format. no need to apply it in the loop. — Siddharth Rout, Dec 13 '21 at 13:08

score 2 · Answer 1 · edited Jan 07 '22 at 23:57

2

Please, try transforming this part:

If Not Cel.Value = "n/a" Then
    Cel.Value = Format(DateValue(Cel.Value), "dd - mm - yyyy")
End If

as:

If Not Cel.Value = "n/a" Then
    Cel.NumberFormat = "dd - mm - yyyy"
    Cel.Value = Format(DateValue(Cel.Value), "dd - mm - yyyy")
End If

edited Jan 07 '22 at 23:57

Peter Mortensen

30,738
21
105
131

answered Dec 12 '21 at 19:15

FaneDuru

38,298
4
19
27

1

@Siddharth Rout Even, better... :) – FaneDuru Dec 12 '21 at 19:17
can you explain it please! why it does not work in single line? – VBAbyMBA Dec 12 '21 at 19:32
1

@Ibn e Ashiq Didn't it work in a single line? What do you mean by "does not work in single line"? It should work... I only tried adapting your existing code inside the loop. But placing such a line before the loop (to format all the range), it will do the job (in a simpler) code. And a little faster, not formatting of each cell... – FaneDuru Dec 12 '21 at 19:42
thanks for detailed answer as I applied `.NumberFormat = "dd - mm - yyyy"` my other formula does not work which is `=DAYS(TODAY(),T2)` – VBAbyMBA Dec 13 '21 at 07:03
@Ibn e Ashiq This means that what you have in column "T:T" **is not formatted as date**, after running your code in this way. I would firstly suggest to delete all your code, keeping only `ActiveSheet.Range("T2:T" & i).NumberFormat = "mm-dd-yyyy"`. It looks that what you said is not true: The column is formatted as `Date`, not as text... It becomes text after applying of your code. The above line formats it (being a Date) as you required and your code creates strings instead... – FaneDuru Dec 15 '21 at 12:31

score 1 · Accepted Answer · answered Dec 12 '21 at 19:42

As I mentioned in the comments above, add

.Range("T2:T" & i).NumberFormat = "dd - mm - yyyy"

before the FOR loop.

Here is another way to achieve what you want without using loops.

Code:

Option Explicit

Sub Date_format()
    Dim ws As Worksheet
    Dim lRow As Long
    
    '~~> Change this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Last row in Col T
        lRow = .Range("T" & .Rows.Count).End(xlUp).Row
        
        With .Range("T2:T" & lRow)
            .NumberFormat = "dd - mm - yyyy"
            
            .Value = ws.Evaluate("index(IF(" & .Address & _
                     "<>""n/a"",DATEVALUE(" & .Address & "),""n/a""),)")
        End With
    End With
End Sub

Screenshot:

Explanation:

This approach uses EVALUATE with INDEX, IF and DATEVALUE to do the conversion without using any loops. For explanation on how it works, please see Convert an entire range to uppercase without looping through all the cells

please see the question I have tried to explain it with pictures — VBAbyMBA, Dec 13 '21 at 07:25

Format string to date with double digit day and month using Excel VBA

2 Answers2