Forum Discussion

anadyer's avatar
anadyer
Copper Contributor
Jan 16, 2020

Putting a date list in DAY order

I have an *.xlsx client list that includes the clients' birthdates. I need to put it in order by the DAY of birth, regardless of the year. I can't find any function that allows me to do so. Is there any way to convert a date such as "11/01/1948" to a matching text field that returns those specific characters as text? That way I could use the RIGHT/LEFT functions to trim it down to just the day. Everything I've tried just returns the serial number of the date which includes the year. The closest I've been able to come is using the WEEK function to convert the date to the week of the year, and then sort on the week. That's close, but we need to be able to group folks from Thursday through Wednesday, rather than Sunday through Saturday. Maybe there's no way to do it, but I'd like to know for sure so I can quit wracking my brain!

 

Formulas and Functions

2 Replies

  • mathetes's avatar
    mathetes
    Gold Contributor
    Jan 16, 2020

    anadyer

    Don't need to make it so complicated. Here's an example. 

    Full date in cell A1

    MONTH(A1)

    DAY(A1)

     

    Then sort by month and day....

    Example attached       (Excel does wonderful things with Date and Time functions. Explore them, using this as your first case study)

    • anadyer's avatar
      anadyer
      Copper Contributor
      Jan 16, 2020

      mathetes 

      Thanks so much! I figured I was trying to make it too complicated, hadn't thought about doing month and day separately then sorting on both. Definitely appreciate the prompt (and effective) response!

Resources