How to get the number of days in a month in Excel

DAY(EOMONTH(A2,0)): days in the month of a date

The sheet lists tenants with their move-in date and monthly rent. To find how many days the move-in month has, take the month's last day with EOMONTH, then take its day number with DAY. The last day of a 30-day month is the 30th, so the answer is 30.

Change 0 to 1 for the number of days in next month: =DAY(EOMONTH(B2,1)) returns 31 for December.

February and leap years

February is the month that breaks lookup tables and hard-coded day counts. EOMONTH follows the calendar rules, so it returns 28 for February 2026 and 29 for February 2028. Century years follow the full rule too: =DAY(EOMONTH(DATE(2100,2,1),0)) returns 28, and 2000 returns 29.

The same idea tells you whether a year is a leap year: =IF(DAY(EOMONTH(DATE(H1,2,1),0))=29,"Leap year","Not a leap year") returns Leap year for 2028.

One quirk: for compatibility with Lotus 1-2-3, Excel treats 1900 as a leap year and accepts February 29, 1900, a date that never existed. It only affects dates before March 1, 1900, so it rarely matters in real files.

From a year and month number, not a date

If you only have a year in H1 and a month number in H2, build a date with DATE. Day 0 of the following month is the last day of this month, so DAY(DATE(year,month+1,0)) is the day count. For December, month+1 is 13, and DATE rolls that into January of the next year, so you do not need a special case.

If the month is a name such as "Feb" in a cell, convert it first; EOMONTH("Feb 2028",0) returns #VALUE! because the text is not a date. A real date in the cell, formatted to show only mmm yyyy, avoids the problem.

Days left in the month, and days so far

Subtract the date from the month end. =EOMONTH(B2,0)-B2 returns 18 for November 12: the days after the 12th. Add 1 if the date itself should count, as it does for a tenant who moves in that day. The days already passed are simply DAY(B2), or DAY(B2)-1 if today should not count yet.

For a live countdown, use TODAY: =EOMONTH(TODAY(),0)-TODAY(). If the result shows as a date like Jan 18, 1900, the cell picked up a date format; set it to General.

Prorating a monthly amount

A monthly charge for part of a month is usually the monthly amount divided by the days in that month, times the days used. For Okafor, who moves in on November 12: 1,450 divided by 30, times 19 days. The same formula gives a full 1,600 for Tanaka, who moves in on the 1st, and 45.83 for Brennan, who moves in on September 30 and pays for one day.

This is not the only method. Some contracts use a 365-day year instead, =ROUND(C2*12/365*(EOMONTH(B2,0)-B2+1),2), which gives 905.75 for Okafor; others count 30 days in every month. Use the method your lease or contract specifies, and put that choice in a labelled cell rather than burying it in the formula.

Working days in a month

For staffing, billing hours or a daily sales target, you often need weekdays, not calendar days. NETWORKDAYS counts Monday to Friday between two dates, including both ends. Give it the first and last day of the month.

Add a holiday list as the third argument to skip public holidays: with November 26, 2026 in it, =NETWORKDAYS(EOMONTH(B2,-1)+1,EOMONTH(B2,0),DATE(2026,11,26)) returns 20. In practice, keep the holidays in a range and point at it.

Days in a quarter or a year

A quarter's days are its last day minus its first day plus one. The first day of the quarter is DATE(YEAR(B2),INT((MONTH(B2)-1)/3)*3+1,1), and EOMONTH of that date two months on is the quarter's last day. The first quarter has 90 days in 2026 and 91 in 2028, because of February.

For a year, subtract January 1 from the next January 1: =DATE(YEAR(B2)+1,1,1)-DATE(YEAR(B2),1,1) returns 365 for 2026 and 366 for 2028.