Knowledge base / Excel

DAY in Excel: extracting the day of the month

I explain the 1-31 result, accurate date inputs and the information lost when a report groups only by the day of the month.

Kajo RudzińskiPublished Updated 5 min read

A highlighted tile is pulled from a blank calendar grid beneath a magnifying lens.

When I need the day of the month from a date, I use DAY. June 15 produces 15. That number no longer tells me whether the original date belonged to June, July or another year. Understanding the information that disappears matters before adding the result to a report.

The examples use small synthetic dates. Their results follow from the function definition and the calendar; I have not run a workbook. English formulas here use commas between arguments. Your regional settings may require another separator. The Polish name of DAY is DZIEŃ.

Syntax and the kind of result

Microsoft defines DAY as returning an integer from 1 to 31 from a value representing a date. It has one required argument:

=DAY(serial_number)

I prefer a reference to a cell containing a genuine Excel date or a result produced by DATE. These examples use modern dates rather than historical date-system exceptions. Microsoft’s documentation covers Excel 2016, 2019, 2021, 2024 and Microsoft 365, among other listed editions.

I leave the result cell formatted as General or Number. A cell that inherits a date format can display the number as another date. I check formatting before deciding that the calculation is wrong. Presentation and the purpose of the calculation need to remain distinct when examining an unexpected result.

A small example step by step

Enter =DATE(2024,6,15) in A2 and =DATE(2024,7,15) in A3. Put =DAY(A2) in B2 and copy it to B3. These synthetic dates make it possible to recognize the result without additional calculations.

Cell Date Formula in column B Result
A2 2024-06-15 =DAY(A2) 15
A3 2024-07-15 =DAY(A3) 15
A4 =DATE(2024,6,1) =DAY(A4) 1

First, the function receives a date. It then selects the component representing the day of the month. The first two rows share that component even though the dates differ. The third row refers to June 1, so its result is 1. None of these results represents a day of the year or the name of a weekday.

DAY, TODAY and DATE perform different jobs

DATE creates a date from a year, month and day. DAY extracts one component. A date constructed directly inside the formula therefore looks like this:

=DAY(DATE(2024,6,1))

The result is 1. The inner function constructs June 1, 2024, and the outer function selects its day. Microsoft’s DATE documentation explains the three inputs. This avoids depending on how a regional configuration interprets a piece of date text.

For the current day of the month, use:

=DAY(TODAY())

Microsoft documents TODAY and its use with DAY. The result depends on the current date when the workbook recalculates. For a historical report, I prefer an explicit reference date so opening the workbook again does not change the meaning of the comparison.

Find the number of days in a date’s month

If A2 contains June 15, 2024, that month ends on June 30. I can find the month-end date first and then extract its day:

=DAY(EOMONTH(A2,0))

The result is 30, the length of June. The zero identifies the month containing the input date. For an input in February 2024, the corresponding result is 29. EOMONTH is documented by Microsoft; its Polish name is NR.SER.OST.DN.MIES. The older Polish version of this entry incorrectly used KONIEC.MIESIĄCA.

The separate entry about finding a month’s final day explains when you need the complete date rather than the number alone. That distinction matters when selecting records from a particular period.

Matching individual day blocks are extracted from different date cards while the other components remain on the cards.

Keep year and month when the analysis needs them

Grouping only by DAY puts June 15 and July 15 in the same group. That can be intentional when exploring activity by day of the month. It cannot, by itself, represent the chronological daily sequence of events. Decide which question the grouping should answer before discarding information from the original date.

I retain the original date beside the helper column so I can inspect which periods were combined. When comparing days of the month, I also consider unequal month lengths. The thirty-first occurs in fewer months than the fifteenth. A count of events alone does not establish frequency relative to the number of available dates.

If you need the number representing a weekday, see WEEKDAY. Similar names can conceal different questions. Describing the required result in an ordinary sentence is a useful check before selecting a function.

Inspect the input before changing the formula

A value that looks like a date can still be text. Applying a date display format does not guarantee that the underlying text becomes a valid date value. During an import, I establish the source format and conversion approach rather than relying on accidental recognition. That is why the small examples construct dates with DATE.

I also look for missing values, errors and timestamps. I do not treat a result obtained from a missing field as valid information about a transaction date. Where the input includes a time, I decide whether the analysis concerns a calendar day or an exact instant. Removing part of the information should be a deliberate analytical choice.

I would not immediately hide source problems with error handling. First, I want to identify records that do not contain valid dates and decide how they should be treated. Depending on the source, they may need correction, exclusion or a separate data-quality report. The formula cannot make that decision on behalf of the analyst.

An exercise with three dates

Use DATE to prepare June 1, June 15 and July 15, 2024. Add DAY beside them and explain which results repeat. You should identify 1, 15, 15 and explain why two different dates can produce the same number.

Then decide which report could use the day column alone and which needs the full date. I would consider the exercise complete when you can explain that distinction. Writing the correct formula is the shorter part of the task.

Key takeaways

What does DAY return in Excel?

A day-of-month number from 1 to 31 for the supplied date. It does not include the month or year and does not represent a weekday.

How do I get the current day of the month?

Use =DAY(TODAY()). The result depends on the current date when the workbook recalculates. Use a fixed reference date for a historical comparison.

Why can different dates produce the same DAY result?

DAY extracts only the day of the month. June 15 and July 15 both produce 15, so retain the full date for chronological analysis.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.