How to count the days in a month in Excel

The simplest way to count the days in a month in Excel is to find its last day: the number of that day is the number of days in the month. It’s simple, but depending on what you have as input (a date, a month number or a month name), you need different combinations of functions. Let’s go through the examples.

Days in the current month

For the current month, you need the TODAY function (returns today’s date), the EOMONTH function (returns the last day of the month) and the DAY function:

=DAY(EOMONTH(TODAY(),0))

Days in a month by date

If cell A1 contains a date and you need the number of days in the same month:

=DAY(EOMONTH(A1,0))

The result is a number, but Excel may format it as a date: set the cell format to General if you see something like “1/31/1900”.

Days in a month by month number

EOMONTH accepts only a date. What if you only have the month number? Build the date with the DATE function:

=DAY(EOMONTH(DATE(2026,A1,1),0))

To keep the example simple, the year is the constant 2026, but you can take it from a cell too. To make the formula work for the current year forever, replace 2026 with YEAR(TODAY()). It returns 2026 in 2026, 2027 in 2027 and so on :)

=DAY(EOMONTH(DATE(YEAR(TODAY()),A1,1),0))

Days in a month by month name

If you want the month to be as clear as possible for users, use a formula that understands the month name. The DATEVALUE function turns text into a date. To be safe, take only the first 3 letters with LEFT: then “January”, “Jan” and “january” all work:

=DAY(EOMONTH(DATEVALUE("1-"&LEFT(A1,3)&"-2026"),0))

The same with the current year calculated automatically:

=DAY(EOMONTH(DATEVALUE("1-"&LEFT(A1,3)&"-"&YEAR(TODAY())),0))

DATEVALUE understands month names in the language of your Windows regional settings. With English names on a system with other settings, it may return #VALUE!.

Related calculations

Is the year a leap year? February has 29 days in it:

=DAY(EOMONTH(DATE(A1,2,1),0))=29

How many working days are in the month of the date in A1? NETWORKDAYS counts Monday to Friday between the first and the last day:

=NETWORKDAYS(EOMONTH(A1,-1)+1,EOMONTH(A1,0))

Add a range with holidays as the third argument to exclude them too.

Summary

I hope you found what you were looking for on this page. The key is always the same: EOMONTH gives the last day of the month, and DAY turns it into the number of days. All that changes is how you build the date: TODAY, DATE or DATEVALUE. Have questions or want to share your formula? Write to me!