Format data as Day-Month-Year instead of Month-Day-year

Jyggalag

Active Member
Joined
Mar 8, 2021
Messages
445
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi all,

I currently have a data set that looks like this:
1660729475905.png


The issue for me is that it is currently stated in a format of "Month-Date-Year". I would like to replace this with a format of "Date-Month-Year".

Does anybody have a nice solution to this, for example a formula as illustrated in my photo?

Thank you all! :)

Kind regards,
Jyggalag
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Are the values in Col A currently text, or are they real dates?
 
Upvote 0
Are the values in Col A currently text, or are they real dates?
Thank you for noting this Fluff!

In the real file, it is rather messy. So some are listed as "date", whereas some are listed as "General".

Furthermore, some sections are already correctly implicated, so I would like a formula, so I may ignore these sections (rather than target an entire column)

I hope that makes sense?
 
Upvote 0
If they are text (which they probably are) you can use Text to columns to convert them to real dates.
Select delimited, next, clear all check boxes, next, select MDY for the date format, Finish.
 
Upvote 0
Solution
If they are text (which they probably are) you can use Text to columns to convert them to real dates.
Select delimited, next, clear all check boxes, next, select MDY for the date format, Finish.
This solved it! And after doing this, they actually also turned to the correct format!

Wow! I am mindblown as always Fluff :) Thank you so much!

I also had a look at your solution @alansidman and this worked out great as well. Thank you so much both of you!!! :)
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top