DAY function

DAY extracts the day of the month (1-31) from a date serial number, useful for filtering tickets and grouping records by calendar day.

=DAY(serial_number)

Generate a DAY formula

Describe what you need. The generator will reach for DAY where DAY is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How DAY reads its arguments
serial_numberrequiredDAY →
ArgumentRequiredDescription
serial_numberRequiredA date value as a serial number, a recognized date string, or a cell reference containing a date; if text or non-date numeric, returns #VALUE! or #NUM!.

Returns

Returns an integer between 1 and 31 representing the day of the month.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Extract the day from a ticket's opened date

Ticket IDPriorityOpenedClosedAgentCSAT
2101High2024-10-052024-10-07Sarah5
2102Medium2024-10-152024-10-18Mike4
2103Low2024-11-012024-11-03Sarah5
2104Critical2024-11-222024-11-24James3
2105Medium2024-11-302024-12-02Mike4
=DAY(C2)

Result: 5

The opened date in C2 is 2024-10-05, which falls on the 5th day of October. DAY extracts only the day component, returning 5. This helps identify when tickets arrived within a month.

2. Find tickets opened in the first week of the month

Ticket IDPriorityOpenedClosedAgentCSAT
2101High2024-10-052024-10-07Sarah5
2102Medium2024-10-152024-10-18Mike4
2103Low2024-11-012024-11-03Sarah5
2104Critical2024-11-222024-11-24James3
2105Medium2024-11-302024-12-02Mike4
=IF(DAY(C3)<=7, "First week", "Later")

Result: "First week"

Ticket 2103 opened on 2024-11-01, so DAY returns 1. Since 1 is less than or equal to 7, IF returns "First week". This categorization helps identify early-month support patterns.

3. Identify tickets opened near month-end

Ticket IDPriorityOpenedClosedAgentCSAT
2101High2024-10-052024-10-07Sarah5
2102Medium2024-10-152024-10-18Mike4
2103Low2024-11-012024-11-03Sarah5
2104Critical2024-11-222024-11-24James3
2105Medium2024-11-302024-12-02Mike4
=IF(DAY(C5)>=25, "Month-end", "Mid-month")

Result: "Month-end"

Ticket 2105 opened on 2024-11-30, so DAY returns 30. Since 30 >= 25, the result is "Month-end". This reveals whether support volume spikes at billing cycle end or month boundaries.

Common errors

Which DAY error are you seeing?
DAY returned an error#VALUE!
Convert text to a date with DATEVALUE: =DAY(DATEVALUE("2024-10-05")). Ensure cells contain actual date values, not text strings.
#NUM!
Verify your date is within the valid range and the serial number is positive. For dates outside Excel's range, use text manipulation instead of date functions.
#N/A
Wrap the lookup in IFERROR to handle missing data: =IFERROR(DAY(VLOOKUP(reference, table, col, FALSE)), "Not found").
ErrorWhy it happensHow to fix it
#VALUE!The serial_number argument contains text that isn't recognized as a valid date format.Convert text to a date with DATEVALUE: =DAY(DATEVALUE("2024-10-05")). Ensure cells contain actual date values, not text strings.
#NUM!The serial_number is negative or falls outside Excel's supported date range (before 1900-01-01 or after 9999-12-31).Verify your date is within the valid range and the serial number is positive. For dates outside Excel's range, use text manipulation instead of date functions.
#N/AThe serial_number argument evaluates to an #N/A from a VLOOKUP, XLOOKUP, or other lookup that failed to find a match.Wrap the lookup in IFERROR to handle missing data: =IFERROR(DAY(VLOOKUP(reference, table, col, FALSE)), "Not found").

Tips and when to use something else

  • Use DAY to segment records by calendar position—find month-starters with DAY(date)=1, identify payroll dates with DAY(date)=15, or spot year-end surges.
  • To find the last day of any month, compare the day to EOMONTH: =DAY(date)=DAY(EOMONTH(date,0)). This accounts for February and varying month lengths automatically.
  • Combine DAY, MONTH, and YEAR to break dates into components, then use DATE() to rebuild them in new orders or with adjusted values.
  • If you need the weekday name (Monday, Friday, etc.) instead of the day number, use WEEKDAY() for a 1-7 code or TEXT(date, "dddd") for full names.

Related functions

DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.MONTHReturns the month (1–12) from any date; use it to organize timesheet entries, identify due dates by month, or detect multi-month tasks.YEARYEAR extracts the four-digit year component from a date serial number, returning an integer representing the calendar year, such as 2024 or 2025.EOMONTHReturns the date of the last day of the month that is a specified number of months before or after a given start date.WEEKDAYWEEKDAY returns the day of the week (1–7) for a given date, with customizable numbering systems (Sunday=1, Monday=1, or ISO 8601).

Frequently asked questions

Can DAY tell me what day of the week a date falls on?
No—DAY returns only the day of the month (1-31). Use WEEKDAY() to get a 1-7 number for the day of the week, or TEXT(date, "dddd") to get the weekday name like "Monday" or "Friday".
What if my dates are stored as text instead of date values?
DAY will return #VALUE! for unrecognized text. Wrap with DATEVALUE: =DAY(DATEVALUE(C2)). Or format the cells as Date type to convert text to actual date values.
Does DAY ignore time information like hours and minutes?
Yes. DAY extracts only the day of the month from a serial number and discards any time component. To extract hours or minutes, use HOUR or MINUTE functions on the same date value.
How do I find records created on the last day of any month?
Use =DAY(date)=DAY(EOMONTH(date,0)) to return TRUE only when the date is the final day of its month. This works correctly for February leap years and months with 28, 29, 30, or 31 days.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17