Excel dates are transposing day and month

Occasional Visitor

I have dates in one of my Excel columns that transposing the day and month on some lines and I can not fix it.


9 Replies


This usually has to do with the system date format (m/d/yyyy vs d/m/yyyy), but without knowing any details it's hard to be more specific.

@MexJane15 Suppose you dates in Column A then use below formula to Column B.




It completed the document partly in Mexico and partly in the US. I can’t change the dates displaying day month year to month day year. Any suggestions?


Can you distinguish between the rows completed in Mexico and those completed in the USA? If so, it might be possible to correct them using a formula or VBA. Otherwise, you're left with a messy situation...

Yes I can tell.


Let's say you have dates in D2 and down. In another column, use a formula like


=IF(entered_in_Mexico, DATE(YEAR(D2),DAY(D2),MONTH(D2)),D2)


where entered_in_Mexico is some kind of criteria.

@Hans Vogelaar 

lets say the date in B2 reads 11/1/2022

and I want to change it to: 1/11/22


How would I write the formula in column H which is empty?


That would be




My Excel dates transpose for segments of a given spreadsheet. And I have not been in Mexico during this extremely aggravating experience. 

It makes my data unusable - it is all date dependent. None of the app commands that are designed to address this effects it in any way.  

The replies to MexJane15 do not seem applicable to my experience.