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:
1drv.ms/x/s!Apno7889bCyLkChJ3...
For more Excel tips, tricks and tutorials don't forget to subscribe to @LearnExceltoexcel channel.
Learn #Excel your way:
===================
Website: learnexceltoexcel.com/
--------------------------------------
Pick your favourite social page:
Fb: bit.ly/3yM3dvd
Tiktok: bit.ly/3wFllpf
Tw: bit.ly/3wGCRYU
Pin: bit.ly/3NsjKIU
Yt: bit.ly/3wxKL8a
Insta: bit.ly/3wtT9oW
Music by: / ikson
#exceltutorial #exceltips #excel #excelformula #excelformulasandfunctions #excelformatting #excelforbeginners #excelformulatypes #excelcustomnumberformatting #text #textfunction #textformatting
Пікірлер: 24
In regular excel versions you can use date function togehter with right, mid and left functions instead of this one
What does \/ mean? Is it only use for forming a date?
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.
thanks worked for me
you are a life saver
Thanks
@LearnExceltoexcel
2 ай бұрын
Welcome
How can we do it manually without excel ?
Amazing 😍😍🤩🤩
@LearnExceltoexcel
Жыл бұрын
🤩🤩🤩🤩
@rhonalkhairul3969
Жыл бұрын
Thanks..what does double "-" mean?
@LearnExceltoexcel
Жыл бұрын
@@rhonalkhairul3969 helps convert numbers stored as text back to numbers.
@LearnExceltoexcel
Жыл бұрын
@@rhonalkhairul3969 converts numbers stored as text back to numbers. Also called double negative operator.
@priyankachourasiya9712
Жыл бұрын
@@rhonalkhairul3969 it means hold the shift key and press " " this invited comma....
Why the formula and formatting? Why not put the format "dd/mm/yyyy" instead? And why does the formula begin with "--"?
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.
@LearnExceltoexcel
Жыл бұрын
Follow this tutorial with the only difference is that your custom format code will be mm/dd/yyyy
--text works in which version ?
@LearnExceltoexcel
Жыл бұрын
text is simply a name of variable. Double dash is there to check if there is any number stored as text so that it can be taken into consideration. As this formula is based on LAMBDA function, you can practice it on Excel for web. Link to practice file is mentioned in the description.
This can also be achieved using date function
@LearnExceltoexcel
Жыл бұрын
Would like to see alternative approaches. Please share your idea.
Hi! Could you please explain why -- before the Text function? Also, why "\/00\/"? Thanks a lot!
@muhammadtambawala6379
Жыл бұрын
This is a good formula. "--" converts text into positive number. Instead of "\/" you can just use "-". For e.g: =--TEXT(A1,"0000\/00\/00") OR use the below. Works same. =--TEXT(A1,"0000-00-00")
@aliciacantaro
Жыл бұрын
@@muhammadtambawala6379 thank you so much!!