How to convert Number in Date

[ThrottleWorks]

Hi,

Please see attached file for your reference.
Column A is my input. Column F is my output.
Value in cell A2 = 20210728. This is not date, if I apply '=YEAR(A2)' it will give me #NUM!

However I need to convert this value in a DATE format, to get this I use nested formula in VBA.
Please cell F2 for the formula.

Can anyone please help me with a a better way in VBA to convert this value in proper date format. Take care.

 

[shrivallabha]

If you want to convert in the same cell then you can test below sample code and then adopt it to loop through the list.

    Dim rng As Range
    Set rng = Range("A2")
    rng.Value = Evaluate("=DATE((LEFT(" & rng.Value & ",4)),(MID(" & rng.Value & ",5,2)),(RIGHT(" & rng.Value & ",2)))")

 

[ThrottleWorks]

Hi @shrivallabha sir, sure, I will do it. Thanks for the help.
Have a nice day ahead.

 

[Marc L]

Can anyone please help me with a a better way in VBA to convert this value in proper date format.

Hi, with the classic Excel way :​

Sub Demo1()
    [E2].Formula = Format([A2], "####/##/##")
End Sub

 

[Debaser]

Simpler formula:

=TEXT(A2,"0000-00-00")+0

or use Data - Text to Columns, with settings Delimited, no delimiters, then specify a date field in YMD format in the last step.

 

[ThrottleWorks]

Hi @Marc L sir and @Debaser sir, thanks a lot for the help. Have a nice day ahead.

@shrivallabha sir, it is working perfect, thanks a lot for the help. Have a nice day ahead.