arrow46 Comments
  1. Michael (Micky) Avidan
    Aug 26 - 8:35 am

    To my opinion there is a shorter & easier way to achieve the task.
    Type the following formula into cell C2 and copy it downwards.
    Sort the table according column “C”.

    =TEXT(B2,”mmm dd”)

    Michael (Micky) Avidan
    “Microsoft® Answer” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)

  2. Michael (Micky) Avidan
    Aug 26 - 8:37 am

    According the Date format in your area it can also be:

    =TEXT(B2,”dd mmm”)

    Michael (Micky) Avidan
    “Microsoft® Answer” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)

  3. Michael (Micky) Avidan
    Aug 26 - 8:53 am

    As for the challenging question:


    Michael (Micky) Avidan
    “Microsoft® Answer” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)

  4. lockdalf
    Aug 26 - 10:38 am

    I wanted to go with sumproduct, but I probably mistyped something so I could not get it working. so I did the next best thing:

    =SUM((MONTH(A1:A100)=MONTH(TODAY()))*1) arrey entered

  5. Utkarsh
    Aug 26 - 10:48 am

    Hi Chandoo,

    I try this formula to find how many birthdays are in this month?


    It showing me correct ans.

  6. Michael (Micky) Avidan
    Aug 26 - 11:50 am


    I always looked at SUMPRODUCT and SUM (array entered) as identical formulas.

    Why didn’t you present the SUMPRODUCT you claim you could not use !?

    I do hope that you understood that the “dash” (–( are 2(!) Minus signs that were glued together in that site reply window.

    Here again is my proposed formula:


    Type a minus sign instead of each(!) x character in the above formula.

    By the way “two minus” signs equal multiplication by: 1

    Michael (Micky) Avidan
    “Microsoft® Answer” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)

  7. RACH
    Aug 26 - 11:53 am

    This formula will also work

    • Michael (Micky) Avidan
      Aug 26 - 9:03 pm

      Too many brackets (at least two) and this is the formula suggested by lockdalf.
      The only difference is (and “it is not”) using SUMPRODUCT instead of SUM as an Array Formula.

      Michael (Micky) Avidan
      “Microsoft® Answer” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)

      • RACH
        Aug 27 - 8:36 am

        Too Many Brackets – Yes, i like to put my formulas in bracket, easy to handle for me
        Same as Lockdalf – i did not notice it before posting.

  8. Martin
    Aug 26 - 2:18 pm


    I have found myself using this same approach with my son’s kinder classmates, so we can tell which ones we should purchase the birthday gift on a given month.

    Mike, as to your proposed formula =TEXT(B2,”mmm dd”) , It works partially, as it needs a little tweak for what Chandoo asked.
    All you need is to enclose it on a VALUE formula, like this:
    =VALUE(TEXT(B2,”mmm dd”)), or in my case, as you well pointed out since I am in Argentina, =VALUE(TEXT(B2,”dd-mmm”). That way, you can sort it by date value, as opposed to the text one, where you can sort it as, well, a text, which will not give you the desired order.

    • Michael (Micky) Avidan
      Aug 26 - 9:34 pm

      I disagree.
      I have tested my suggestion several times and to my opinion there is no need for the VALUE function in order to sort(!) the DOB as per DAY-MONTH ascending order.
      Have a look at the hereunder link which shows the data before sorting by column “B”

      If this does not work at your end – please upload a simple “Excel” file in which you use the: =TEXT(B2,…)) formula, and present a link to the download address of that file.

      Michael (Micky) Avidan
      “Microsoft® Answer” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)

      • Arie
        Aug 26 - 9:58 pm

        for me
        =TEXT(B2, “mmm dd”) (for today gives “Aug 26”)
        =TEXT(B2, “dd mmm”) (for today gives “26 Aug”)

        give results that do not give the requested sort order.

        maybe for your locale setting it might give output that works, but it is not a general solution for the question asked by Chandoo.

    • Michael (Micky) Avidan
      Aug 26 - 9:58 pm


      Please read again Chandoos question/task.
      To my opinion – he did not explained himseld prpperly.
      If you use the VALUE function than the formula will return an Integer number which represents the FULL(!) DOB including the YEAR – and therefor the range will be sorted by “AGE” – something Chandoo wanted to avoid.

      It is possible that I misunderstood the task.
      Let us wait for Chandoo to reply.

      Michael (Micky) Avidan
      “Microsoft® Answer” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)

  9. Vishal
    Aug 26 - 4:41 pm

    Here’s the gift and where’s the cake!

    Assuming that the DOB are in column B starting with row 2:
    1. Enter in C2, the formula =Text(month(b2),”00″)&”-“&text(day(b2),”00”)?
    2. Copy it down.
    3. Sort column C.
    This will list the birthdays in mm-dd format.

    As for the birthdays falling in current month, let’s assume that the list of birthdays is as per column B starting with row 2. The formula in cell C2 would be =month(B2)=month(today()). List all the “TRUE” and discard all the “FALSE”.

    Am I wrong? Or was this too simple?

  10. Pedro Wave
    Aug 26 - 5:20 pm

    My homework: Two array formulas (Ctrl + Shift + Enter)


    Into the last formula, notice that the NOW() function is essential to work fine 😉

  11. Michael (Micky) Avidan
    Aug 26 - 5:53 pm


    With all due respect the use of NOW() is NOT essential.
    Instead of NOW() you can type a single comma.
    The use of: “” is also unnecessary.

    Try: =COUNT(IF(MONTH(A1:A100)=MONTH(TODAY()),))

    Michael (Micky) Avidan
    “Microsoft® Answer” – Wiki author & Forums Moderator
    “Microsoft®” MVP – Excel (2009-2014)

  12. Pedro Wave
    Aug 26 - 7:38 pm

    @Michael, you’re absolutely right.

    If not write the optional arguments when the months are equal returns 0’s and if the months are different returns False, counting the 0’s, as it is expected.

    When I wrote the function NOW() was to make a little joke (did you see the emoticon?) and your answer are very serious 😉

    • Michael (Micky) Avidan
      Aug 27 - 7:08 am


      1) Sorry for not noticing the emoticon.
      2) I always try to be serious while dealing with “Excel”.

      Michael (Micky) Avidan
      “Microsoft® Answer” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)

      • Pedro Wave
        Aug 27 - 8:28 am


        1) You are forgiven.
        2) I always try to have fun while I’m learning and to be awesome while I write one of my blog posts. Did you read it? 😉

        • Michael (Micky) Avidan
          Aug 27 - 12:51 pm

          No – haven’r read.
          What about a direct link !?

  13. Arie
    Aug 26 - 7:57 pm

    This does not work for people born on Feb 29 if the current year is not a leap year.
    An alternative is “=MONTH(B2)*100+DAY(B2)” and sort in numeriek order.

  14. Venky
    Aug 27 - 1:52 am

    How about using sumifs.


  15. zurman
    Aug 27 - 7:25 am

    Why not by sort command?

    • Michael (Micky) Avidan
      Aug 27 - 7:45 am

      You will understand, why not, if you’ll read the Chandoos question again…

      Michael (Micky) Avidan
      “Microsoft® Answer” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)

  16. Robert Clark
    Aug 27 - 8:10 am

    I think that if I was asked for a list of birthdays, I would probably want them forward from the current date (i.e. – not interested in those that have past), so my formula becomes:



    • Michael (Micky) Avidan
      Aug 27 - 9:51 am

      Tis is what we use to call “A waste of ink”.
      Your suggestion can be shorter by, at least, 67% !!!


      *** In older versions, of “Excel”, the user must mark the Analysis ToolPak add-in for the EDATE to function properly.

      Farther to the above – using my short formula will enable you to congratulate a 29/2/2008 born child on the 28/2/2014 (a day before) and not on the 1/3/2014 (a day “too late”).

      Michael (Micky) Avidan
      “Microsoft® Answer” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)

      • JD Hen
        Aug 27 - 3:03 pm

        I would prefer a solution without the need for an add-in, even if it is readily available.

        That said, could you explain the edate and datedif functions, as I’ve not experienced them before?

        • Michael (Micky) Avidan
          Aug 27 - 3:19 pm

          @JD Hen,

          1) As for the EDATE Function – please refere to the build-in Help.

          2) As for the DATEDIF FFunction:
          It is known for many many years but, unfortunatelly, Microsoft didn’t bother to add it to their Help engine (exceptional is “Excel 2000”).
          Have a look at Chip pearsons site:

          Michael (Micky) Avidan
          “Microsoft® Answer” – Wiki author & Forums Moderator
          “Microsoft®” MVP – Excel (2009-2014)

  17. Robert Clark
    Aug 27 - 11:05 am

    @Michael: Clever! I like!

  18. John E E Fleming (jesqrdf)
    Aug 27 - 2:47 pm

    Hi Chandoo,
    I put my birthday reminder list in Month & Day order by adding an extra column and simply typing into the cells a number equal to (MONTH number) + the (DAY number)/100. (e.g. for 23 March Type 3.23) and the sorting by that column.

    Following your bright suggestion :-) I changed to
    birthdate in cell A1
    and in cell B1=(MONTH(A1))+(DAY(A1)/31).
    Then SORTing by column B will put the Birthdates in Month & Day order.
    The inclusion 29 Feb is not a problem.
    The divisor shown as 31 can of course be any number!

    Alternatively B1=100*MONTH(A1)+(DAY(A1) would work just as well (formatting as a leading zero makes it look better e.g 17 Jan shows as 0117)

    As I have people with same ‘Month+Day’ so I have added another column C1= YEAR(A1) and then SORTed by column B and col C to put them into age order.

    • Chandoo
      Aug 28 - 1:57 am

      Very interesting technique John…

    • Shaji
      Aug 28 - 4:27 am

      All Interesting…
      I personally feel that the formula provided by Mr. Chandoo is better and simplest one. In order to solve the leap year problem, we modify the same formula as follows…
      Since year “2000” is a leap year, it always returns the correct answer.

      – Shaji

  19. MF
    Aug 29 - 2:37 am

    Hi all, it’s really a pleasure to learn from you through from posts.

    @Micheal, maybe there is a typo in your 1st post that leads to some misunderstanding… I think you mean “mm dd” instead of “mmm dd”

    • Michael (Micky) Avidan
      Sep 03 - 5:27 pm


      As you probably noticed we, in Israel, use a completely different months names (in Hebrew).

      But, I am curious to know – what did you(!) get, as the result, when using:

      =TEXT(B2,”mmm dd”)

      Michael (Micky) Avidan
      “Microsoft® Answer” – Wiki author & Forums Moderator
      “Microsoft®” MVP – Excel (2009-2014)

      • MF
        Sep 06 - 3:37 am

        I see where the misunderstanding came from, as I was totally unaware of the regional differences in month name…

        FYI, I get
        “Sep 06″ for =TEXT(today(),mmm dd” (won’t sort well)
        “09 06″ for = TEXT(today(),mm dd” (sort well)


  20. […] Sort by Birthday […]

  21. Chris Walker
    Sep 03 - 3:11 am

    This is really useful for sorting anniversaries for anything really (in addition to people’s birthdays)!

  22. Connie
    Sep 05 - 7:34 pm

    Hi everyone, thanks for giving my brain a workout! To determine birth month and day, I entered =TEXT(B2,”mm.dd”) down column C. Then, to determine how many birthdays in this month, I entered =COUNTIF(C2:C100,”09.??”).


  23. Haseeb A
    Sep 07 - 1:10 am

    To count DOB in current month:

    Note: If there is at least one BLANK cell in A1:A100, using MONTH will incorrect results when current month is Jan. Because excel treat blanks cells are 1/0/1900 for 1904 1/0/1904. Either add one more condition in SUMPODUCT (A1:A100″”) or use TEXT function like,



  24. Matt Healy
    Oct 10 - 2:27 am

    What if somebody on the list was born on 29 Feb in a leap year, but current year is not a leap year?

    • Dilip Kumar
      Apr 02 - 11:00 am


  25. Dilip Kumar
    Apr 02 - 10:58 am


  26. Birpal Singh
    Feb 03 - 7:15 am

    I have 4 Sheets in my excel sheets Same Sheets. (Sheet1-Sheet4)
    Coloum A Have : Location/Place
    Coloum B Have : Name
    Coloum C have : Birthdays
    Coloum D Have : another details.

    Now I want to know on Sheet 5 Like a Summary of Only “Today” Birthdays.
    Today [Date: 03/Feb/2015] Birthday List
    Coloum A: Location/Place
    Coloum B: Name
    Coloum C : Total Age/Year Completed.

    Please Help on This

Leave a Reply

Mobile Theme