+ Reply to Thread
Results 1 to 12 of 12

How can I sort a column of military times from earliest to latest?

  1. #1
    Registered User
    Join Date
    10-24-2020
    Location
    New Zealand
    MS-Off Ver
    2019
    Posts
    16

    Question How can I sort a column of military times from earliest to latest?

    I have a column with military times that I would like to sort from the earliest time to the latest time and also sort the column next to it with it.


    The earliest time on the list is 1:15 with M next to it in the next column. I'd like to keep the letters next to each time. And also, how can I get it to ignore blank cells in the column and just any cell that doesn't contain military time format?

    I have attached an example.


    Thanks.
    Attached Files Attached Files
    Last edited by acmarston; 10-17-2021 at 07:29 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,511

    Re: How can I sort a column of military times from earliest to latest?

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    10-24-2020
    Location
    New Zealand
    MS-Off Ver
    2019
    Posts
    16

    Re: How can I sort a column of military times from earliest to latest?

    I didn't want to include the whole thing.
    Last edited by AliGW; 10-17-2021 at 07:06 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,511

    Re: How can I sort a column of military times from earliest to latest?

    I didn't ask you to.

    From the instructions to which I pointed:

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) ...

  5. #5
    Registered User
    Join Date
    10-24-2020
    Location
    New Zealand
    MS-Off Ver
    2019
    Posts
    16

    Re: How can I sort a column of military times from earliest to latest?

    Is a table good enough?
    Last edited by AliGW; 10-17-2021 at 07:20 AM. Reason: PLEASE stop quoting unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,511

    Re: How can I sort a column of military times from earliest to latest?

    No. We need to see the way that the data is formatted.

    I don't understand why you think it's hard to post a workbook containing a small sub-set of your real data. It would save so much guesswork.

    Hopefully someone will be willing to help you without one.

  7. #7
    Registered User
    Join Date
    10-24-2020
    Location
    New Zealand
    MS-Off Ver
    2019
    Posts
    16

    Re: How can I sort a column of military times from earliest to latest?

    Have attached an example of what I'm talking about.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    Re: How can I sort a column of military times from earliest to latest?

    Try

    =INDEX(A$1:A$22,MATCH(SMALL($A$1:$A$22,ROWS($1:1)),$A$1:$A$22,0))

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

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,511

    Re: How can I sort a column of military times from earliest to latest?

    I selected columns A and B, then went to the Data ribbon and clicked the button to sort A to Z. Is this what you want?

    AliGW on MS365 Insider (Windows) 32 bit
    A
    B
    1
    01:15
    M
    2
    01:30
    Q
    3
    02:20
    O
    4
    04:10
    N
    5
    04:55
    P
    6
    15:25
    F
    7
    16:10
    C
    8
    16:35
    G
    9
    16:45
    D
    10
    16:53
    I
    11
    17:14
    A
    12
    17:45
    H
    13
    17:55
    E
    14
    18:10
    K
    15
    18:24
    B
    16
    18:38
    J
    17
    20:05
    L
    18
    21:41
    R
    19
    20
    21
    22
    Sheet: Sheet1

  10. #10
    Registered User
    Join Date
    10-24-2020
    Location
    New Zealand
    MS-Off Ver
    2019
    Posts
    16

    Re: How can I sort a column of military times from earliest to latest?

    Seems to work now. Thanks.
    Last edited by acmarston; 10-17-2021 at 08:05 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,511

    Re: How can I sort a column of military times from earliest to latest?

    Select B5 to I26.

    Data | Sort | Time (from drop-down) - smallest to largest.

    AliGW on MS365 Insider (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    5
    Date: RM RN Time: NOH DSFLR FPLR MBW FN
    6
    01/07/2020
    Greyville
    7
    01:15
    12
    19
    2
    0.05
    Bhakka
    7
    Tipperary
    4
    01:30
    12
    6
    1
    NA Lord Lariat
    8
    Musselburgh
    7
    02:20
    9
    17
    2
    0.75
    Lady Shanawell
    9
    Kempton Park
    2
    04:10
    12
    11
    1
    NA Tommy Rock
    10
    Leopardstown
    4
    04:55
    12
    7
    1
    NA Reverberation
    11
    Randwick Kensington
    2
    15:25
    8
    15
    2
    0.5
    Jimmy's Dream
    12
    Sandown Lakeside
    4
    16:10
    8
    15
    1
    NA Grand De Flora
    13
    Randwick Kensington
    4
    16:35
    8
    15
    2
    2
    Darleb
    14
    Sandown Lakeside
    5
    16:45
    10
    15
    3
    0.8
    Maximak
    15
    Doomben
    5
    16:53
    8
    20
    1
    NA Merlin's Magic
    16
    Belmont
    2
    17:14
    12
    15
    2
    2.69
    Convalesce
    17
    Randwick Kensington
    6
    17:45
    10
    19
    1
    NA Le Gai Soleil
    18
    Sandown Lakeside
    7
    17:55
    13
    11
    2
    2.25
    Seas No Limit
    19
    Balaklava
    7
    18:10
    13
    15
    1
    NA Kahaylaan
    20
    Belmont
    4
    18:24
    11
    15
    1
    NA Avalon Bay
    21
    Doomben
    8
    18:38
    13
    12
    3
    1.3
    Roman Aureus
    22
    Sha Tin
    7
    20:05
    10
    18
    2
    0.1
    Mig Energy
    23
    Redcliffe
    5
    21:23
    8
    7
    1
    NA Tinka Terror
    24
    Bathurst
    5
    21:41
    9
    8
    3
    1.1
    Sammi Dance
    25
    Redcliffe
    6
    21:53
    8
    7
    3
    3
    Storm Gilbert
    26
    Bathurst
    6
    22:11
    10
    9
    3
    4.3
    My Buzz Lightning
    Sheet: Sheet1

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,511

    Re: How can I sort a column of military times from earliest to latest?

    Thanks for marking the thread as solved.

    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 all those who offered help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find earliest and latest date
    By krzysiekpirat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2021, 03:05 AM
  2. [SOLVED] Averaging the earliest start times and latest end times for multiple days
    By kbiro in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-26-2016, 02:15 PM
  3. Macro to Sort earliest shift to latest shift
    By Shellybelly in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-05-2014, 09:00 AM
  4. [SOLVED] Identifying earliest and latest times
    By Ron Purpura in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2014, 10:56 AM
  5. [SOLVED] How to get a total 'time in minutes' from a column that has military times entered
    By bustech in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2013, 07:11 AM
  6. [SOLVED] How do you end military times in column to 'total minutes'
    By bustech in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2013, 01:35 PM
  7. [SOLVED] Calculate time durations for a roster, and indentify the earliest and latest times
    By bajdr47 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-25-2012, 04:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1