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

@MexJane15 

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.

=TEXT(A1,"MMM YYYY")

Harun24HR_0-1660272549808.png

 

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?

@jkcjane420 

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.

@jkcjane420 

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?

@jkcjane420 

That would be

 

=DATE(YEAR(B2),DAY(B2),MONTH(B2))

@MexJane15 

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.