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.
Number of 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))
| A | B | C | |
|---|---|---|---|
| 1 | Date | Days in month | Working days |
| 2 | 10/22/2026 | 31=DAY(EOMONTH(A2,0)) | 22=NETWORKDAYS(EOMONTH(A2,-1)+1,EOMONTH(A2,0)) |
| 3 | 02/10/2028 | 29=DAY(EOMONTH(A3,0)) | 21=NETWORKDAYS(EOMONTH(A3,-1)+1,EOMONTH(A3,0)) |
| 4 | 02/01/2026 | 28=DAY(EOMONTH(A4,0)) | 20=NETWORKDAYS(EOMONTH(A4,-1)+1,EOMONTH(A4,0)) |
| 5 | 04/30/2026 | 30=DAY(EOMONTH(A5,0)) | 22=NETWORKDAYS(EOMONTH(A5,-1)+1,EOMONTH(A5,0)) |
EOMONTH(date,0) is the last day of the month, and DAY of it is the number of days. February 2028 has 29 days. NETWORKDAYS from the first to the last day counts Monday–Friday.
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))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month # | Name | By number | By name | Leap year? |
| 2 | 2 | February | 28=DAY(EOMONTH(DATE(2026,A2,1),0)) | 28=DAY(EOMONTH(DATEVALUE("1-"&LEFT(B2,3)&"-2026"),0)) | FALSE=DAY(EOMONTH(DATE(2026,2,1),0))=29 |
| 3 | 2 | Feb | 29=DAY(EOMONTH(DATE(2028,A3,1),0)) | 29=DAY(EOMONTH(DATEVALUE("1-"&LEFT(B3,3)&"-2028"),0)) | TRUE=DAY(EOMONTH(DATE(2028,2,1),0))=29 |
| 4 | 9 | September | 30=DAY(EOMONTH(DATE(2026,A4,1),0)) | 30=DAY(EOMONTH(DATEVALUE("1-"&LEFT(B4,3)&"-2026"),0)) | FALSE=DAY(EOMONTH(DATE(2026,2,1),0))=29 |
| 5 | 12 | Dec | 31=DAY(EOMONTH(DATE(2026,A5,1),0)) | 31=DAY(EOMONTH(DATEVALUE("1-"&LEFT(B5,3)&"-2026"),0)) | FALSE=DAY(EOMONTH(DATE(2026,2,1),0))=29 |
DATE builds the 1st of the month from its number; DATEVALUE reads “1-Feb-2028” from the first three letters of the name, so both “February” and “Feb” work. The year is written into the formulas here so that 2026 and 2028 can be compared.
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!.
| A1 | Formula | Result |
|---|---|---|
| 3/15/2026 | =DAY(EOMONTH(A1,0)) | 31 |
| 2 | =DAY(EOMONTH(DATE(2028,A1,1),0)) | 29 |
| April | =DAY(EOMONTH(DATEVALUE("1-"&LEFT(A1,3)&"-2026"),0)) | 30 |
Leap years and working days in a month
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.
Like the article? Support its author! !SEMTools is a set of hundreds of tools that solve many tasks without typing complex formulas and save your time.
Summary: which days-in-month formula to use
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!