Sort dates from old to new in different formats

Billie16

Board Regular
Joined
Oct 11, 2009
Messages
52
Hey I'm sorting these dates from old to new in Excel.
All have date formats but the format is different.

21/11/2017
22/11/2017
24/11/2017
27/03/2018
19/04/2018
06/03/2018-07/03/2018
07/12/2017-08/12/2017
08/02/2018-09/02/2018
08/03/2018-09/03/2018

As you can see Excel is able to sort the dates in this format correct dd/mm/yyyy but in this format it goes wrong dd/mm/yyyy-dd/mm/yyyy (for example 07/12/2017 ) Bot formats are in the same column. Why is this and how can I sort it correctly?

I'm using office 2010. Same problem in newer versions.

Thank you.

<tbody>
</tbody>
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
An Excel date is a number.
21/11/2017 is translated into an Excel date
08/03/2018-09/03/2018 is not a number it is text.
So the dates won't get sorted rpoperly.

Use a Helper column.
With your dates in column A

in B1
=IF(ISNUMBER(A1),A1,LEFT(A1,8)+0)
If the value in column A is a number then it is an actual date, otherwise it extracts the first 8 characters of the text and by adding 0 turns it into a number.
So i9n either case you end up with an Excel date that is a number.

Now sort via column B and A
 
Upvote 0
An Excel date is a number.
21/11/2017 is translated into an Excel date
08/03/2018-09/03/2018 is not a number it is text.
So the dates won't get sorted rpoperly.

Use a Helper column.
With your dates in column A

in B1
=IF(ISNUMBER(A1),A1,LEFT(A1,8)+0)
If the value in column A is a number then it is an actual date, otherwise it extracts the first 8 characters of the text and by adding 0 turns it into a number.
So i9n either case you end up with an Excel date that is a number.

Now sort via column B and A

Thanks but if I use this formula it says this formula contains an error. (The refference to A1), it's the semicolon or colon that u use.
Formula is good.

But If I use this formula it works but =IF(ISNUMBER(G17);G17;LINKS(G17;8)+0) this is te result I get:
06/03/2020
07/12/2020
08/02/2020
08/03/2020

That aren't the correct dates.
It doesn't do the trick. I've seen versions of excel in wich it works.

06/03/2018-07/03/201806/03/2020
07/12/2017-08/12/201707/12/2020
08/02/2018-09/02/201808/02/2020
08/03/2018-09/03/201808/03/2020
09/01/2018-10/01/201809/01/2020
11/09/2017-12/09/201711/09/2020

<colgroup><col span="2"></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0
Sorry my mistake try this

=IF(ISNUMBER(G17);G17;LINKS(G17;10)+0)
 
Last edited:
Upvote 0
Sorry my mistake try this

=IF(ISNUMBER(G17);G17;LINKS(G17;10)+0)

Thanks but doesn't work because Excel places the 2017 dates at the bottom.
It makes 07/12/2020 wich shouldn't be last from old to new since it should come before the 2018 dates.

6/03/2018-07/03/201806/03/2020
08/02/2018-09/02/201808/02/2020
08/03/2018-09/03/201808/03/2020
09/01/2018-10/01/201809/01/2020
11/09/2017-12/09/201711/09/2020

<tbody>
</tbody>

07/12/2017-08/12/201707/12/2020

<tbody>
</tbody>
 
Last edited:
Upvote 0
BTW It does look like Excel uses and knows this format is a dateformat but with an *
when I look al the number value it says Date but like this *14/03/2001 (for cellvalue 11/09/2017-12/09/2017).
It doesn't say number, standard or text.
 
Upvote 0
No. This works perfectly.

If you REPLACE the formula I originally gave you with the more recent one you shouldn't get any 2020 year dates.

I dont know what language or what country or what version of Excel you are using.
I am using the English version of Excel 2017 and if I enter this in column B

=IF(ISNUMBER(A1),A1,LEFT(A1,10)+0)

it works fine.

Adjust whatever you need to in that formula for your country.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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