Forum Discussion
Excel interprets dates inconsistently ddmmyy vs mmddyy
When importing a text file with dates in the dd/mm/yyyy format excel incorrectly interprets them as mm/dd/yyyy wherever it can when copied to a new spreadsheet. Eg it interprets 13/05/2019 as 13 May 2019 correctly but interprets 12/05/2019 in the next cell as 05 December 2019
regardless of the format applied to both the source and destination cells being dd/mm/yyyy.
3 Replies
What is your default date format for OS? And if you import by wizard do you apply Date format on third step?
Hi Sergei. You mentioned the Wizard so I have used that method and problem solved. Obviously the Wizard allows you to correctly specify the format so no problemws.
I don't know why I didn't use that in the first place
My system (Windows) is set to d/mm/yyyy
I export from a banking system to CSV
I open the CSV and the date format is d/mm/yyyy
I copy dates to clipboard and paste as values into a dd/mmm/yyyy formatted column in an XLSM spreadsheet and it interprets any dd below 13 as the month and the mmm as the day
for any dd above 12 it interprets it correctly as the dd and the mm as the mmm
