Flip cell value/Date problems

[scaffnull]

Hello Excel community, 

I was wondering if there is a way to flip around numbers within a cell?

So if I have a lot of cells that are formatted wrong with dates.

So how the cell look is this: DDDD dd/mm, and when I have it like that I can't filter and sort when I create my slicer in a pivot table, it only shows each date, but I'd like to have it with months etc,

Is there is a way to change and flip so I get mm/dd?

So how the cells looks like now for example- 

Monday 23/05

Wednesday 25/05

And in another column I have the year (see below)

Or is there some solution concate the two columns so I get excel to "understand" the column are dates.

Thanks for any help.

[#2 · Accepted solution]

@scaffnull 

 There is a way to flip the date format in Excel cells from “DDDD dd/mm” to “mm/dd”.

 To do this you can use a formula to extract the day and month values from the original date and then concatenate them in the desired order. 

Here’s an example formula that does this:

=RIGHT(A1,2)&"/"&MID(A1,FIND("/",A1)-2,2)

This formula extracts the last 2 characters from cell A1 (which should contain the original date in the format “DDDD dd/mm”) and concatenates them with a “/” separator and the 2 characters before the “/” character in cell A1.

If you have the year value in another column, you can concatenate it with the result of this formula to create a complete date in the format “mm/dd/yyyy”.

Here’s an example formula that does this:

=B1&"/"&C1

This formula concatenates the value from cell B1 (which should contain the result of the first formula) with a “/” separator and the value from cell C1 (which should contain the year).

After you have created this formula, you can copy it down to all rows that contain dates in the original “DDDD dd/mm” format.

This will create a new column with dates in the desired “mm/dd/yyyy” format that Excel can recognize and use for filtering and sorting.

Hope this helps!

[#3]

@NikolinoDE Thank you so much, that did it!