Date formatting not working

[Peterino]

Hi. This has been a problem on various files for years and I can't seem to solve it. In this case I downloaded bank transactions from a few different banks and removed all but the dates. The format I want them in is month, day , year which would look like 11/04/2021 for today's date. Currently the column lists the date with the day/mth/yr as 04/11/2021. I want to switch the day and month around.

I have tried the following which works most of the time.
1. Format column. Date, mm/dd/yyyy.
2. Text to Columns, delimited, MDY.
Tried both of these but I can't figure out how to reverse the day and month.

Could use some help on this.

Thank you.

Peter

[alansidman]

The only way I could manipulate the dates were to bring them into Power Query. Split them based upon the / symbol and then recombine them.

Here is the Mcode

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[MrShorty]

I wonder what you did differently, because, when I use Text to columns, it seems to work just fine:

1) Select column A
2) Call Text to Columns -> at the appropriate step, choose Date and DMY for the date type.
3) Finish text to columns and evaluate.

For the sample data, all dates are converted from text to number. Spot checking the actual dates, all dates appear to be correct.

[Peterino]

Hi MrShorty. Yes, it works with day/month/year. I want to change to month/day/year

[MrShorty]

After using text to columns to convert from text to number, it should be a simple matter of applying an appropriate number format. My copy of Excel defaults to a month/day/year format, so I don't need to apply the number format. If your system defaults to date/month/year, then apply a suitable date format like "mm/dd/yyyy".

Edit to add: Remember that, in the text import wizard, you are telling Excel how to read the text date. You are not telling Excel how you want it formatted after the import. Use number formatting after text to columns to get the desired format.

[Peterino]

Wow! I actually took some time and went through how to use the Power Query function. Never too old to learn.

Much appreciated.

Peter

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.