How to swap date and month digits in excel

0
votes

I have a date format of mm-dd-yyyy (03-12-2018). Excel reads it as 3rd December 2018. The actual date is 3rd March 2018, I want to convert it into 12th March 2018 in dd-mm-yyyy format.

Let's assume our data is in cell B2 and it reads 03-12-2018 i.e 3rd December 2018 I have tried =Date(right(B2,4),mid(B2,4,2),left(B2,2)). It gives 12-02-3440 as output due to excel date-time code.

2
exceldateformat
Your example is not that of swapping date and month; rather it is an example of writing the day value into both the month and day fields. Which do you want? – Ron Rosenfeld
Once you figure out what you want to do, you can convert using the DATE, DAY & MONTH functions. – Ron Rosenfeld
Sorry for the mistake, i want it as 12th March 2018 – user8625135
I have tried it, its not working. – user8625135
Edit your question to show what you have tried, and explain what you mean by not working – Ron Rosenfeld

2 Answers

2
votes

This most likely will not solve your underlying problem, but to swap month-day in an Excel date, you can use:

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

Then format the cell to show whatever date format you want.

However, if your underlying problem stems from opening a csv or txt file, you'll need to change that method to an import method to sort out your various date issues.

2
votes

Try this solution:
convert both wrong date and wrong text(likely also happen to you that some dates are not recognized as date but as text)

This is convert a DD/MM/YYYY into MM/DD/YYYY format regardless whether original format seen as date or text.

=DATE(RIGHT(TEXT(Cell,"MM/DD/YYYY"),4),MID(TEXT(Cell,"MM/DD/YYYY"),4,2),LEFT(TEXT(Cell,"MM/DD/YYYY"),2))