Returns the day of the month corresponding to a date. Extracts the day component (1-31) from a date serial number, essential for date analysis, age calculations, and day-based operations.
Master the fundamentals of Excel DAY function
The DAY function extracts the day component from a date serial number, returning a value between 1 and 31. This function is fundamental for date analysis, enabling day-based calculations, age calculations, and date component manipulation. DAY works with Excel's date serial number system where dates are stored as numbers.
Extracts the day component (1-31) from any valid date, representing the day of the month.
Returns a number between 1 and 31, varying based on the month length and the specific date.
Works with Excel's date serial number system, accepting dates in various formats including serial numbers, date strings, and cell references.
Returns #VALUE! for invalid dates, ensuring data integrity in calculations and preventing silent errors.
Function-specific parameters
Function-specific return type
Extract day component for precise age and birthday calculations
Analyze data by day of month for patterns and trends
Implement day-based business rules and validations
Validate dates and check date ranges based on day values
Exact matching required
Returns numeric position
Handles missing text gracefully
=DAY(serial_number)The date for which you want to find the day. Can be a date serial number, date string, or cell reference containing a date.
The day of the month as a number (1-31)
Description: Extracts the day component from a date
Extract day from a specific date
Returns 15 for the 15th day of the month. DAY extracts the day component from the date serial number.
Using DAY function in VBA
Sub DAYExample()
Dim result As Integer
Dim myDate As Date
myDate = DateValue("2024-01-15")
result = Day(myDate)
Range("A1").Value = result
MsgBox "DAY(2024-01-15) = " & result
End Sub
' Extract day from cell
Sub ExtractDayFromCell()
Dim cellValue As Date
Dim dayValue As Integer
cellValue = Range("A1").Value
dayValue = Day(cellValue)
Range("B1").Value = dayValue
End Sub
' Loop through dates and extract days
Sub ExtractDaysFromRange()
Dim cell As Range
Dim dayVal As Integer
For Each cell In Range("A1:A10")
If IsDate(cell.Value) Then
dayVal = Day(cell.Value)
cell.Offset(0, 1).Value = dayVal
End If
Next cell
End SubExtract day component for age calculations
Analyze patterns by day of month
Determine if date is month end
Implement day-based business rules
DAY returns #VALUE! error
=DAY("invalid date")Solution: Ensure the argument is a valid date. Check that the cell contains a date serial number, not text. Use DATEVALUE() to convert text dates, or verify cell formatting is set to Date.
DAY returns unexpected values
=DAY(A1)Solution: Verify the input is actually a date. Text that looks like dates may be interpreted incorrectly. Ensure cells are formatted as dates and contain valid date values. Use ISDATE() to verify before using DAY.
Want day with suffix (1st, 2nd, 3rd)
=DAY(A1)Solution: Use custom formatting or TEXT function with DAY. For ordinal suffixes, use: =DAY(A1)&IF(OR(DAY(A1)={1,21,31}),"st",IF(OR(DAY(A1)={2,22}),"nd",IF(OR(DAY(A1)={3,23}),"rd","th")))