Format Cell by Date

[jfish]

Hi,

I am trying to sort my excel sheet by dates.
The problem is that some cells have different format of dates (some include the year and some are not)

13/09/2017
14/09

Using Format cell function they are now look the same.
13/09
14/09

A few are not converting nicely

so 12/9/2017 becomes 09/12 instead of 12/09

However when trying to sort them by year it's messes up because the real value of the cell still has the 2017 in it.

After sorting:

Date
09/04
09/05
09/07
09/12
09/12
13/09
28/08
29/08
31/08

[63falcondude]

Please upload a sample workbook of your data.

[jfish]

https://ufile.io/9kzvp

[63falcondude]

The problem here is that we have no way of knowing if 09/12 is supposed to be September 12th or December 9th. Can we assume that these are all in 2017?

Are the dates that you shared in post #3 supposed to be dates in August and September only? If so, try this:

B2 =IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(2017,RIGHT(A2,2),LEFT(A2,2)))

[johnnymac]

It seems that some of your dates are not being stored and read as dates by Excel. In other words, they are not seen as a number of days past Jan. 1, 1900 - Windows date format. And the ones it does read as dates are formatted in the European fashion (i.e. 09/12 = December 9th). Check your original source for these values.

[jfish]

The problem here is that we have no way of knowing if 09/12 is supposed to be September 12th or December 9th. Can we assume that these are all in 2017?

Are the dates that you shared in post #3 supposed to be dates in August and September only? If so, try this:

B2 =IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(2017,RIGHT(A2,2),LEFT(A2,2)))

Thank you! works like a charm.

Best way to automate it to apply for A Columns for future addition of dates?

Some sort of a Macro?

Right now I just copy paste values back to Column A.