We use the MM/DD/YYYY format in the datasets. This might not be the date format you’d like, but with this guide, you can learn how to convert the cell value to any date format or convert date to text.
Working with dates in Excel can be quite irritating. If you want to make date-based calculations, your cells must be recognized and properly formatted.
Converting Text Format to Date Format
Let’s assume you have 05/03/2019 in the cell A1. This actually translates to May, 3rd 2019. Let’s convert it to date format.
1) Check the A1 cell whether it is recognized by Excel or not. To do so, double click onto a blank cell and type
=A1+12) If Excel doesn’t recognize it as a date, you need to convert 05/03/2019 to “May, 3rd 2019” or any date format you like in this dialogue format.
Apply this simple formula to convert:
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))Converting Date Format to Text Format
Using text strings comes up handy when you don’t want to deal with Excel dates or maybe you want to import data to any other database as the cell lengths are always consistent in text format.
If you need to convert the A1 cell May, 3rd 2019 to a text format like 05/03/2019, you can do this with a simple formula:
=IF(LEN(MONTH(A1))=1,"0"&MONTH(A1),MONTH(A1))&"/"&IF(LEN(DAY(A1))=1,"0"&DAY(A1),DAY(A1))&"/"&YEAR(A1)