Excel DAY function


The Excel DAY function is designed to extract the day of the month from any given date value, returning it as a number between 1 and 31. It’s a straightforward and useful function for breaking down dates into their individual components, particularly the day part, which can then be used in further calculations or analyses.
  • date: The date from which you want to extract the day. It must be a valid Excel date.
Return value
A number (1-31) representing the day of the month from the given date.

How to use

To use the DAY function, simply provide a valid Excel date as the argument. The function will return the day part of that date as a number. It’s particularly useful when you need to isolate the day component of a date for comparison, calculation, or as input into another function.


Simple DAY

Extracting Day from a Date: To retrieve the day component from a specific date:

This formula extracts and returns the day part of December 25, 2020, which is 25.
Creating a Specific Date with Dynamic Day: To dynamically create a date with a specific month and year, but varying day:
					=DATE(2021, 7, DAY(A1))
Assuming A1 contains a date, this formula uses the day from A1 and constructs a new date in July 2021 with that day.
DAY with IF
Day-Based Messaging: To display a message based on the day of the month:
					=IF(DAY(B1)=1, "First Day!", "Not the First Day")
This formula checks if the day in B1 is the first of the month and returns “First Day!” if true, or “Not the First Day” otherwise.
DAY for Age Calculation
Calculating Days Until Next Birthday: To calculate how many days are left until the next birthday from a birth date:
					=DAY(TODAY()) - DAY(C1)
Assuming C1 contains a birthdate, this formula calculates the number of days from today until the next occurrence of the day in C1.

Additional Notes

  • The DAY function is especially useful in date manipulation and calculations involving the specific days of the month.
  • Remember that the input date must be a valid Excel date; otherwise, the function will return a #VALUE! error.
  • The DAY function is part of a group of functions that break down dates into their components, alongside MONTH and YEAR, which can be used together for comprehensive date analysis.

Related Functions

Excel DATE function

The Excel DATE function creates dates from year, month, and day components, ensuring dynamically constructed dates are valid.

Excel YEAR function

The Excel YEAR function extracts the year from a date, converting it to a number, useful for year-based analysis.

Excel DAY function

The Excel DAY function extracts the day of the month from a date, returning a number between 1 and 31, useful for date breakdowns.

Excel MONTH function

The Excel MONTH function extracts the month from a date, returning a number from 1 to 12, ideal for calculations and scheduling.

Content Navigation