Jun 23 2020 11:47 AM
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.
Jun 23 2020 11:55 AM
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
Jun 23 2020 12:01 PM
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
Jun 23 2020 12:02 PM
Sep 10 2024 01:56 PM