Forum Discussion
Solved
Date Format in Excel
I have a large multi-year Excel file that I occasionally add more information from a vendor. I use the "01/01/18" date format, but recently their files started using a "2018-01-01". Is there a way to...
Hi Gary,
If your local date format is MM/DD/YY when first records are most probably text. Try to select entire column, in ribbon Data->Text to Columns and on third stage of the wizard select Date for the field with YMD format.
I have tried that already, and it did not work. I have attached an example of part of the file that shows
the 2 different formats that are in the date column. I appreciate your help...Gary
You can modify the date as you want using the DATE function to strip the components of a string and join them back as the date you want - see an example.
In my example the
- year is RIGHT(TEXT(A2,"dd/mm/yyyy"),4)
- month - MID(TEXT(A2,"dd/mm/yyyy "),4,2)
- day - LEFT(TEXT(A2,"dd/mm/yyyy"),2)
A2 is the date I strip into components
If excel recognizes all your entries as dates you need the text function
If theay are strings just right(A2,4) is enough,you do not need the text function - TEXT(A2,"dd/mm/yyyy")
I use this to convert form US format when not recognized - see sample
I