If you want to pull the day number from a date, the DAY function returns an integer from 1 to 31.
In Excel 2021 and later, you can feed DAY a range and its results spill into the cells below.
In this article, I’ll show you how to extract day numbers, find the number of days in a month, and identify first-of-month dates.
DAY Function Syntax in Excel
The DAY function needs one date value.
=DAY(serial_number)
- serial_number is required. It can be a cell containing a real Excel date, a date serial number, or the result of another date formula.
DAY returns the Gregorian day of the month, regardless of how the source date is displayed.
When to Use DAY Function
- Extract the day of the month from a list of dates.
- Find how many days are in a month by combining DAY with EOMONTH.
- Flag records that fall on the first day of a month.
- Rebuild or shift dates with DATE, YEAR, MONTH, and DAY.
- Calculate the first date of each month from any date in that month.
Example 1: Extract Day Numbers from Dates
We’ll start with the basic spilling form.
Below is the dataset with milestone IDs, completion dates, and an empty Day of Month column.
I want one day number for every completion date in B2:B9.
Here is the formula in C2:
=DAY(B2:B9)
DAY reads all eight dates and spills the results through C9. The returned numbers are 3, 14, 1, 27, 9, 31, 12, and 25.
The result is a number, not a formatted date. Keep the result cells in General or Number format if Excel displays them as dates.
Pro Tip: Excel 2019 and earlier do not support this spill. Use =DAY(B2) in C2 and fill the formula down instead.
Example 2: Count Days in Each Month
Here’s a useful combination with EOMONTH.
Below is the dataset with billing cycles, one sample date from each cycle, and an empty Days in Month column.
I want to return the number of days in the month containing each sample date.
Here is the formula in C2, filled down through C7:
=DAY(EOMONTH(B2,0))
EOMONTH returns the last date in the same month because its second argument is zero. DAY then extracts that date’s day number.
February 2024 returns 29 because it is a leap year, while February 2025 returns 28. The remaining months return 30 or 31.
This example uses a separate formula in each row, which keeps each billing cycle easy to inspect and copy independently.
Example 3: Flag First-of-Month Dates
This example turns a day number into a review flag.
Below is the dataset with permit IDs, issue dates, and an empty First-Day Review? column.
I want “Review” beside permits issued on the first day of a month and a blank result for every other date.
Here is the formula in C2:
=IF(DAY(B2:B8)=1,"Review","")
DAY returns an array of day numbers. Comparing that array with 1 produces TRUE for dates on the first and FALSE for the rest.
IF converts those results into “Review” or an empty string. PR-701, PR-703, PR-705, and PR-707 are flagged.
Pro Tip: The empty string in the third IF argument makes nonmatching rows look blank. Those cells still contain the spilled formula’s results.
Example 4: Rebuild a Date Two Years Later
Here’s a date-rebuilding formula with a leap-day detail worth seeing.
Below is the dataset with equipment purchase dates and an empty Warranty End column.
I want to rebuild each purchase date two years later using its year, month, and day parts.
Here is the formula in C2:
=DATE(YEAR(B2:B6)+2,MONTH(B2:B6),DAY(B2:B6))
YEAR supplies the year and adds two. MONTH and DAY keep the original month and day before DATE builds each new date.
Most rows keep the same month and day in 2026. The February 29, 2024 purchase becomes March 1, 2026 because 2026 has no February 29.
That rollover is normal DATE behavior. If a leap-day warranty needs a different rule, decide that business rule before using this pattern.
Example 5: Return the First of the Month
This last example moves each date back to its month start.
Below is the dataset with newsletter names, send dates, and an empty Month Start column.
I want the first calendar date of each send date’s month.
Here is the formula in C2:
=B2:B6-DAY(B2:B6)+1
DAY returns how far each date sits into its month. Subtracting that number moves one day before the month began, so the final +1 reaches day 1.
The spilled results are March 1, April 1, June 1, August 1, and December 1, 2026.
Pro Tip: Format the result cells as dates. Excel stores dates as serial numbers, so a General-formatted result may display a five-digit number.
Tips & Common Mistakes
- DAY returns the day of the month. It does not return a weekday number or calculate the number of days between two dates.
- Use real Excel dates. Text that looks like a date can be interpreted differently across regional settings. Cell dates or results from DATE are safer inputs.
- Check the result format. A DAY result should normally use General or Number format. Date formatting can turn a small result such as 3 into January 3, 1900.
- Expect values from 1 through 31. DAY never returns 0 for a valid date.
- Watch leap-day rollovers. Rebuilding February 29 in a non-leap year with DATE can move the result into March.
- Know the difference between DAY and DAYS. DAY extracts one component of a date. DAYS measures the interval between two dates.
I use DAY for simple date-part extraction and as a building block inside larger date formulas. These examples cover spilling, month lengths, first-day flags, date rebuilding, and month starts.
I hope you found this article helpful.
