Excel Pro Trick: Format numbers in Correct Date Format in Excel with TEXT function in Excel formula

Learn how to format 8 digit numbers to correct date format using Excel formula and custom number formatting in Excel. The formula approach works even when Flash Fill outputs wrong results.

In this Excel tutorial, we learn how to use to Excel TEXT function with a very lesser known formatting code to first convert numbers to correct dates and then we use Excel custom number formatting to fix the date format in desired format.

In this tutorial, we also learn how we can use double negatives "--" to conveniently change numbers stored as text back to number values so that we can format them in shape of appropriate dates.

Lets Excel!

Excel Workbook to Download or Practice online for this tutorial:

For more Excel tips, tricks and tutorials don't forget to subscribe to @LearnExceltoexcel channel.

Learn #Excel your way:
===================
--------------------------------------

Pick your favourite social page:

#exceltutorial #exceltips #excel #excelformula #excelformulasandfunctions #excelformatting #excelforbeginners #excelformulatypes #excelcustomnumberformatting #text #textfunction #textformatting

[ajiko]

In regular excel versions you can use date function togehter with right, mid and left functions instead of this one

[wqqwate]

In my opinion. yyyy-mm-dd is the only correct way to display dates as it is the most logical way to sort being in excel or files. From bigger to smaller demoninators.

[morling.]

What does \/ mean?
Is it only use for forming a date?

[dzakahwonder]

How can we do it manually without excel ?

[Innovlrnr]

This can also be achieved using date function

[garlicbreadcrumbs]

Hi, i have date data that goes like this 23-02-2023 in dd-mm-yyyy order. Apparently older excel doesn't recognize this as date so I can't subtract with =today() (ex: =(B3-today()). Excel only recognize date that written like this 02/23/2023 (in mm/dd/yyyy order) so the subtract formula can work. My question is, how do i change my date data from this (23-02-2023) to this (02/23/2023) without manually typing it? Thanks in advance.

[aliciacantaro]

Hi! Could you please explain why -- before the Text function? Also, why "\/00\/"? Thanks a lot!

[steves]

Why the formula and formatting?
Why not put the format "dd/mm/yyyy" instead?
And why does the formula begin with "--"?