Master the DAY Function: Extract Day Numbers from Dates in Excel
=DAY(serial_number)The DAY function is one of Excel's most straightforward yet powerful date manipulation tools, designed specifically to extract the day component from any date value. Whether you're working with project timelines, financial reports, or customer data, understanding how to isolate the day from a complete date is essential for data analysis and reporting. This beginner-friendly function returns a number between 1 and 31, representing the day of the month, making it invaluable for creating dynamic schedules, calculating business metrics, and organizing date-based information. The DAY function works seamlessly across all modern Excel versions, from Excel 2007 through Excel 365, ensuring compatibility whether you're using legacy systems or the latest cloud-based applications. By mastering this function, you'll unlock the ability to perform sophisticated date analysis, create conditional formatting rules based on specific days, and build complex formulas that intelligently process temporal data. This guide will walk you through everything you need to know to use DAY effectively in your spreadsheets.
Syntax & Parameters
The DAY function follows a simple yet elegant syntax: =DAY(serial_number). The serial_number parameter is required and represents the date from which you want to extract the day value. In Excel, dates are stored internally as serial numbers, where January 1, 1900 equals 1, and each subsequent day increments by one. You can provide the serial_number in multiple formats: as a date entered directly into the cell (like 2024-03-15), as a cell reference pointing to a date (like A1), as a result from another function (like TODAY() or DATE()), or as text formatted as a date (like "3/15/2024"). The function returns an integer between 1 and 31, depending on the day of the month in the provided date. If you input an invalid date or a non-date value, Excel will return a #VALUE! error. The beauty of the DAY function lies in its reliability and consistency—it works identically across all Excel versions and integrates seamlessly with other date and time functions. When combining DAY with functions like MONTH() and YEAR(), you can completely deconstruct any date into its individual components, enabling advanced date calculations and analysis workflows that would otherwise require complex manual processing.
serial_numberPractical Examples
Extracting Day from Today's Date
=DAY(TODAY())The TODAY() function returns the current date, and DAY extracts just the day component. If today is March 15, 2024, this formula returns 15.
Finding Days Remaining in Month
=DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)This advanced formula calculates the last day of the current month by creating a date for the first day of next month, subtracting one day, then extracting the day number. For March 2024, it returns 31.
Extracting Day from a Specific Date Cell
=DAY(A2)If cell A2 contains the date 2024-06-22, this formula extracts and returns 22. This can be copied down the entire column to process multiple dates.
Key Takeaways
- The DAY function extracts the day of the month (1-31) from any date value in Excel
- It works seamlessly with date functions like TODAY(), DATE(), MONTH(), and YEAR() for comprehensive date analysis
- The function is compatible across all Excel versions from 2007 through 365, ensuring universal spreadsheet compatibility
- DAY returns #VALUE! errors when given invalid dates, so always validate your date inputs before processing
- Combining DAY with conditional logic enables powerful business analytics based on when dates occur within the month
Pro Tips
Use DAY with SUMIF to sum values based on whether dates fall on specific days of the month. For example, =SUMIF(A:A,">=1",B:B) combined with DAY logic can identify end-of-month transactions.
Impact : Enables sophisticated financial analysis where month-end or month-start dates have special significance
Combine DAY with SEQUENCE function in Excel 365 to create dynamic calendars or day-of-month indicators without manual entry.
Impact : Saves time on repetitive date formatting tasks and creates more maintainable spreadsheets
Use DAY in data validation rules to prevent users from entering dates outside acceptable day ranges, such as =AND(DAY(A1)>=1,DAY(A1)<=31).
Impact : Improves data quality and prevents invalid date entries before they cause problems downstream
Cache DAY results in a helper column before using them in complex formulas with thousands of rows to improve calculation performance.
Impact : Significantly reduces spreadsheet calculation time, especially in large workbooks with volatile formulas
Useful Combinations
Complete Date Deconstruction
=DAY(A1)&"/"&MONTH(A1)&"/"&YEAR(A1)This formula reconstructs a date in MM/DD/YYYY format by extracting day, month, and year separately. Useful for reformatting dates or creating custom date strings for reporting.
Identify Dates in Specific Day Range
=IF(AND(DAY(A1)>=15,DAY(A1)<=31),"Late Month","Early Month")This combination uses DAY with IF and AND functions to categorize dates as occurring in the early or late portion of the month, useful for business cycle analysis.
Calculate Days Until Month End
=DAY(DATE(YEAR(A1),MONTH(A1)+1,1))-DAY(A1)This advanced combination calculates how many days remain in the month from a given date by finding the last day of the month and subtracting the current day.
Common Errors
Cause: The serial_number parameter contains text that Excel cannot recognize as a valid date, such as "March 15" without a year or "15-03-2024" in an unexpected format.
Solution: Ensure the date is properly formatted or use the DATE() function to construct a valid date. Verify your date format matches your Excel locale settings. Use =DAY(DATEVALUE(A1)) if you're working with text strings that need conversion.
Cause: The function name is misspelled as =DAY() or there's a typo in the formula syntax, such as =DAY(A1 without closing parenthesis.
Solution: Check the spelling of the function name carefully. Ensure all opening parentheses have matching closing parentheses. Use Excel's formula autocomplete feature to avoid typos.
Cause: The serial_number is outside Excel's acceptable date range (January 1, 1900 through December 31, 9999) or represents an invalid date like February 30.
Solution: Verify that your date falls within Excel's valid range. Check for data entry errors in date columns. Use data validation to prevent invalid dates from being entered.
Troubleshooting Checklist
- 1.Verify that the cell reference or date value you're using actually contains a date, not text that looks like a date
- 2.Check your Excel locale settings to ensure date formats are being recognized correctly (Date → Language settings)
- 3.Confirm that the date falls within Excel's valid range (1/1/1900 to 12/31/9999)
- 4.Test the formula with a known date like TODAY() to isolate whether the issue is with your input or the function itself
- 5.Ensure you're using proper syntax with opening and closing parentheses and no extra spaces
- 6.If working with text dates, use DATEVALUE() to explicitly convert them before passing to DAY()
Edge Cases
Leap year dates like February 29, 2024
Behavior: DAY correctly returns 29 for leap year February dates
No special handling needed; Excel automatically recognizes leap years
Dates stored as serial numbers (e.g., 45000) instead of formatted dates
Behavior: DAY processes serial numbers correctly and returns the appropriate day value
Solution: The function works transparently with serial numbers since that's how Excel stores dates internally
Serial number 45000 equals approximately March 28, 2023, and DAY returns 28
Using DAY with negative or zero serial numbers
Behavior: Returns #NUM! error because negative numbers and zero are outside Excel's valid date range
Solution: Ensure all dates are positive numbers within the valid range (1 = 1/1/1900)
This is a data validation issue rather than a function limitation
Limitations
- •DAY only returns the day of the month (1-31) and cannot extract other temporal units like quarter, week number, or day of year without additional functions
- •The function cannot perform calculations with days; it only extracts the day component. To calculate days between dates, use DAYS() or subtraction instead
- •DAY returns #VALUE! error if the input is not recognized as a date, requiring error handling with IFERROR() in production spreadsheets
- •The function is dependent on Excel's date system (which starts from January 1, 1900), limiting its use for historical dates before 1900 or dates beyond December 31, 9999
Alternatives
Compatibility
✓ Excel
Since 2007
=DAY(serial_number) - Identical syntax and behavior across all versions including 2010, 2013, 2016, 2019, and 365✓Google Sheets
=DAY(date) - Google Sheets uses the same function name and syntaxGoogle Sheets handles date formats slightly differently; use DATE() function for consistency across platforms
✓LibreOffice
=DAY(date) - LibreOffice Calc supports the DAY function with identical syntax to Excel