Forum Discussion
Gary Thompson
Mar 26, 2018Copper Contributor
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.
- Damien_RosarioSilver Contributor
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
- Gary ThompsonCopper Contributor
- Stanislav IvanovCopper Contributor
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