Forum Discussion
Conversion from European Date Format
I'm using Excel 365. I just copied a column of dates from a web page and pasted them into a column in Excel. The dates on the web page were in the European date format (i.e., dd/mm/yyyy). When pasted, they looked the same (dd/mm/yyyy) and the formatting for the filled cells was "General." I want to be able to sort this column by date, and thought I could reformat the entries using the "format cells" function to show them in a mm/dd/yyyy format. It doesn't work.
How do I convert the dates to a usable format that I can sort?
Thanks.
Hi barshfield ,
See if this helps.
=DATE(VALUE(RIGHT(A2,4)), VALUE(MID(A2,4,2)), VALUE(LEFT(A2,2)))
This is assuming the "European" date is in Cell A2
See my Christmas example below.
Ben
- THANK YOU! I've been struggling for the past few days and after trying the above method, it worked. I can now move onto conditional formatting.
Most probably you pasted dates as texts, if so only format won't help. Select column with such dates, Data->Text to Columns and on third step of the wizard select Date and DMY. Finish. If my guess is correct you shall see the dates now in your default format. You may change it on another one selecting the column again, Ctrl+1, Number, Custom format, mm/dd/yyyy
- Hello,
For one-off formatting, you can use
1. Select the data
2. CRTL + 1 and select Custom
3. Enter the following: mm/dd/yyyy
4. Click OK
However, if you want to make the changes permanent on your system to have mm/dd/yyyy format, kindly follow the steps below:
1. Type in Control Panel on your system
2. Under Clock and Region, click on Change date, time or number formats
3. Click on Additional Settings
4. Select Date tab
5. In the Date Formats section, clear what you have there and type in dd/mm/yyyy
5. Click OK and OK.
When you return to Excel, all the date with d mm yyyy will turn to dd/mm/yyyy
Do let me know how it pans out after following the steps
