Home

Excel interprets dates inconsistently ddmmyy vs mmddyy

%3CLINGO-SUB%20id%3D%22lingo-sub-678951%22%20slang%3D%22en-US%22%3EExcel%20interprets%20dates%20inconsistently%20ddmmyy%20vs%20mmddyy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678951%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20importing%20a%20text%20file%20with%20dates%20in%20the%20dd%2Fmm%2Fyyyy%20format%20excel%20incorrectly%20interprets%20them%20as%20mm%2Fdd%2Fyyyy%20wherever%20it%20can%20when%20copied%20to%20a%20new%20spreadsheet.%20Eg%20it%20interprets%2013%2F05%2F2019%20as%2013%20May%202019%20correctly%20but%20interprets%2012%2F05%2F2019%20in%20the%20next%20cell%20as%2005%20December%202019%3C%2FP%3E%3CP%3Eregardless%20of%20the%20format%20applied%20to%20both%20the%20source%20and%20destination%20cells%20being%20dd%2Fmm%2Fyyyy.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-678951%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678959%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20interprets%20dates%20inconsistently%20ddmmyy%20vs%20mmddyy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356932%22%20target%3D%22_blank%22%3E%40Jonhollisbrown%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20your%20default%20date%20format%20for%20OS%3F%20And%20if%20you%20import%20by%20wizard%20do%20you%20apply%20Date%20format%20on%20third%20step%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679214%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20interprets%20dates%20inconsistently%20ddmmyy%20vs%20mmddyy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679214%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20system%20(Windows)%20is%20set%20to%20d%2Fmm%2Fyyyy%3C%2FP%3E%3CP%3EI%20export%20from%20a%20banking%20system%20to%20CSV%3C%2FP%3E%3CP%3EI%20open%20the%20CSV%20and%20the%20date%20format%20is%20d%2Fmm%2Fyyyy%3C%2FP%3E%3CP%3EI%20copy%20dates%20to%20clipboard%20and%20paste%20as%20values%20into%20a%20dd%2Fmmm%2Fyyyy%20formatted%20column%20in%20an%20XLSM%20spreadsheet%20and%20it%20interprets%20any%20dd%20below%2013%20as%20the%20month%20and%20the%20mmm%20as%20the%20day%3C%2FP%3E%3CP%3Efor%20any%20dd%20above%2012%20it%20interprets%20it%20correctly%20as%20the%20dd%20and%20the%20mm%20as%20the%20mmm%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679231%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20interprets%20dates%20inconsistently%20ddmmyy%20vs%20mmddyy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679231%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei.%20You%20mentioned%20the%20Wizard%20so%20I%20have%20used%20that%20method%20and%20problem%20solved.%20Obviously%20the%20Wizard%20allows%20you%20to%20correctly%20specify%20the%20format%20so%20no%20problemws.%3C%2FP%3E%3CP%3EI%20don't%20know%20why%20I%20didn't%20use%20that%20in%20the%20first%20place%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jonhollisbrown
New Contributor

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

@Jonhollisbrown 

 

What is your default date format for OS? And if you import by wizard do you apply Date format on third step?

@Sergei Baklan 

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 

@Sergei Baklan 

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