changing numbers into date

[vivienkong]

I want to change the number 28022023 into 28/02/2023 format. Kindly advice how how to do it. The number is actually a transaction date provided from a statement. I want to format it into 28/02/2023 so that I rearrange the whole statement from early to current date.

[#2]

@vivienkong 

with format cells...

Format numbers as dates or times

with formula:

=TEXT(A2,"m/d/yyyy")

Steps:

How to convert serial number to date in Excel?

[#3 · Accepted solution]

@vivienkong 

Let's say the number is in A1:

=DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))

Then choose desired cell formatting for date.

[#4]

Thank you Nikolino

[#5]

Thank you Patrick

[#6]

The above formula works for date 10 to 31. How to re-word the formula for date 1 to 9?
12052021 12/5/2021
12052021 12/5/2021
29042021 29/4/2021
6042021 30/7/2024
6042021 30/7/2024

[#7]

For those last two dates, I don't see where 2024 is coming from. Those appear to be 2021 dates?

[#8]

for the date is ddmmyyyy (which the date is from 10 to 31) , the formula works fine.
for date that is dmmyyyy (which the date is from 1 to 9), the formula jumps 24 days, 3 months and 3 years ahead

[#9]

@vivienkong 

Convert dates stored as text to dates

How to convert serial number to date in Excel?

[#10]

what if the date is like this 1302023 = 1/30/2023 and then the next line is 12252022 = 12/25/2022 ?@vivienkong