CODEWITHSUNDEEP
excelsorting
morg
morg

Reputation: 1175

Sort by horodate on Excel

I would like to sort my Excel file by horodate so when we switch from summer time to winter time data is ordered by chronological order and so that when I create a graph the data is represented in the right order.

When i sort by horodate (from a to Z) i obtain that:

      Horodate                  Value       Chronological order
    2014-10-26T01:30:00+02:00   32273345000 1
    2014-10-26T02:00:00+01:00   28310393000 4
    2014-10-26T02:00:00+02:00   31705207000 2
    2014-10-26T02:30:00+01:00   27116547000 5
    2014-10-26T02:30:00+02:00   29900587000 3
    2014-10-26T03:00:00+01:00   26535312000 6
    2014-10-26T03:30:00+01:00   26165805000 7
    2014-10-26T04:00:00+01:00   26022551000 8

Which is not the chronological order as you see.. I would like to have 1 2 3 4 5 6 7 8

Thank you

Upvotes: 0

Views: 42

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

You can add helper column and sort using it:

enter image description here

Formula in D1 (dragged down) is:

=SUBSTITUTE(LEFT(A1,19),"T"," ")-RIGHT(A1,5)*(MID(A1,20,1)&"1")

First part (SUBSTITUTE(LEFT(A1,19),"T"," ")) calculates date and time part, second (-RIGHT(A1,5)) timezone offset, and the third (MID(A1,20,1)&"1") sign of timezone offset (you can remove it, if there is always + in horodate).

Upvotes: 2

Related Questions