Wednesday, January 18, 2012

Excel Formula to get Number of Days in a Month

It seems Excel still can't tell me the days in a particular month.

The following formula works, though:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

Obviously, A1 has to have a date in it.

Works like a charm:




 Update, much, much later: Looks like LibreOffice has a DAYSINMONTH function.

20 comments:

  1. Really excellent. Thanks.

    ReplyDelete
  2. Michael (Micky) AvidanJuly 6, 2013 at 2:49 PM

    No need or such a "long" formula.

    In B2 type: =DAY(EOMONTH(B3,0))

    Michael Avidan
    “Microsoft®” MVP – Excel
    ISRAEL

    ReplyDelete
  3. Thanks Micky!

    EOMONTH may not always be available it seems: http://office.microsoft.com/en-001/excel-help/eomonth-HP005209076.aspx - so if anybody can't use Micky's cool shorter version, the original formula should still do the trick :)

    ReplyDelete
  4. Thanks Mr. Nils & Mr.Micky

    ReplyDelete
  5. Awesome, one click answer, thanks.
    Now, can we calculate just the weekdays in a month with a similar method?

    ReplyDelete
  6. I want to get result by giving name of month like "January", "February" etc. instead of given date in cell B3. Is it possible? if yes, anybody please help me soon. Thanks

    ReplyDelete
  7. Saiful, that's simply a matter of assigning the cell a custom date format, I'd say.

    ReplyDelete
  8. THANKSSSSSSSSSSSSSSSSSSSSS

    ReplyDelete
  9. how do we get for previous month is there
    any formula for that

    ReplyDelete
  10. Hey Linto,

    There seems to be no "get previous month" function in OpenOffice (and I don't have MS Office installed right now), so I came up with this formula which seems to work:

    =DATE(YEAR(a1);MONTH(a1)-1;1)

    It gives you the first day of the previous month of a date in field a1. To get the length of that previous month, just use the same formulas from this post on the date you have thus obtained.

    ReplyDelete
  11. Thanks a lot

    ReplyDelete
  12. thanks a lot......perfect formula to get a number of days in a month.....

    ReplyDelete
  13. Thanks A lot

    ReplyDelete
  14. Nishant Kumar VermaAugust 30, 2016 at 7:37 AM

    If possible I want to get result by giving name of month like “January”, “February” etc. instead of given date in cell A1. Is it possible? if yes, anybody please help me soon @ nishantverma8391@gmail.com

    ReplyDelete
  15. Thanks the formula works

    ReplyDelete
  16. @Nishant ..did u get the solution..pls post,if yes

    ReplyDelete