Excel interprets all date formats as US - Stack Overflow
How to get Excel to correctly recognize imported American dates, or convert them to European format?
java dateformat - How to change US dates to UK format in Microsoft Excel (mmm/dd/yyyy to dd/mm/yyyy) - Stack Overflow
Excel - How to convert US dates and times to UK dates and times
Videos
I have a spreadsheet with a list of burial dates from England and the age they died. The dates are in the day/month/year format (12/02/1854). Is there anyway to convert it to month/day/year?
For Excel 2010, rather than opening your CSV file, create a new workbook, then on the DATA tab, select Get External Data → From Text. This gets to the interface where you can specify how to interpret your text data, including how to handle dates.
Change the file's extension from ".csv" to ".txt", then open with Excel. Excel will give you a text import wizard. Select 'delimited' on the first page, check 'comma' on the second, and on the third you'll be able to select the type for each column of data. One of the types is date and has a drop down with a variety of formats (m/d/y, d/m/y, etc, etc).
Hi Proficient N,
Thank you for reaching out. My name is Leo, a user just like you. I'd be happy to help you with your concern.
Your Excel is set to the default mm/dd/yyyy format. To change it to dd/mm/yyyy, you must select all the cells that contain your dates. Then, go to the Data tab > Text to columns > Delimited > Next > Uncheck all delimiters > Next > Date > DMY > Finish. Once done, check if the dates have changed to the format you want.
Your understanding and patience will be highly appreciated. I hope I provided a helpful solution regarding the issue! Let me know if you have any further questions. Be safe always!
Best Regards,
Leo
Hi Leo Marcha,
Thank you so much. Your reply helped me solve the problem.
My excel default setting is DD/MM/YYYY and the source data was in MM/DD/YYYY, so under the text-to-column I selected MDY and that worked correctly.
Here is a solution without using a formula:
- Select the relevant columns
- Choose Data → Text to Columns…
- Select “Delimited” and click Next
- Untick all delimiters and click Next
- Select data column format “Date: MDY” and click Finish
The dates should now be converted to UK dates.
The problem is that a US date can parsed by Excel as a UK date when the day is less than 13. When this happens Excel converts it to the localized UK serial (date) number.
So 03/19/2014 is obviously a US date of the 19th of March. However 05/03/2014 is ambiguous so Excel parses it the local date format as the 5th of March, rather than the US 3rd of May. Any formula has to check if Excel has stored the US Date as a UK date. A UK date will be stored in Excel as a number.
=IF(ISNUMBER(A2),DATE(TEXT(A2,"yyyy"),TEXT(A2,"dd"),TEXT(A2,"mm")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))
(For a US date in cell A2 and PC date is dd/mm/yy).
If ISNUMBER is true, the US date looks like a UK date and Excel has serialized it as a number. So can format the date as text and back to a date again. Note day is passed to the month parameter of the first DATE function to perform the conversion.
If ISNUMBER is false, its stored as a string as Excel doesn't convert a date string with >12 months. So use string functions to split it up for the DATE function.
Hi Hans
Thanks for your prompt response.
I apologise in advance for my ignorance, but I don't know how to run a Macro. Can you let me know how to do it.
Thanks in advance.
Hi Hans
Thank you for your prompt reply. Unfortunately it doesn't work. For unknown reason, Excel doesn't recognise the data as dates.
I have tried formatting the cell to: dd/mm/yyyy hh:mm but nothing changes and the data remain the same (ie American format)