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 convert their dates back to the format I use? Thanks in advance for any advice....Gary
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.
Hi Gary
Try this:
1. Highlight the cells/columns that contain the Dates.
2. Right click and choose Format Cells.
3. Choose Date in the left side menu.
4. Under Date, ensure that you select the format that you want (Might also be worth checking the locale to ensure that the dates are displayed in the right order for your region).
Select the appropriate format and click OK.
This should hopefully do the trick.
Hope it helps.
Cheers
Damien
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
