DAY function

The DAY function in Excel is used to extract the day of the month from a given date. It returns a number between 1 and 31, representing the day of the month.

Syntax

DAY(serial_number)

Parameters

• serial_number: This is the date from which you want to extract the day. The serial_number can be a valid Excel date, a reference to a cell containing a date, or a date represented as a text string that Excel recognizes.

How It Works

The DAY function extracts the day part from a valid date, which is the day of the month, as a number between 1 and 31. Excel internally stores dates as serial numbers, and the DAY function isolates the day component of that serial number.

Examples

1. Extracting the Day from a Date: Suppose you have the date “2025-01-15” in cell A1. To extract the day from this date:

   =DAY(A1)
   

   Result: 15

2. Direct Date Input: If you type a date directly into the function, such as “December 25, 2025”:

   =DAY("2025-12-25")
   

   Result: 25

3. Using with TODAY(): To extract the current day of the month, you can combine the DAY function with TODAY:

   =DAY(TODAY())
   

   Result: This will return the current day of the month, for example, “13” if today is January 13, 2025.

4. Using with a Date in Text Format: If you have a date in text format (e.g., “March 9, 2025”) in cell B1, you can use the DAY function as follows:

   =DAY(DATEVALUE(B1))
   

   Result: 9

Common Use Cases

• Extracting the Day from a Date: When you have a full date and only want the day of the month, such as when analyzing sales by day or organizing events.
• Calculating Day of the Week: You might want to combine DAY with other date functions (like WEEKDAY) to perform more complex calculations, such as finding the day of the week.
• Dynamic Date Reports: When working with dates dynamically (e.g., with the TODAY function), you can use the DAY function to generate reports that show day-specific data.

Important Notes

• Valid Date: The input must be a valid date format. If the serial number or date is not recognized, DAY will return a #VALUE! error.
• Excel Date System: Excel dates start from January 1, 1900 (serial number 1). Ensure that your date input is a valid serial number or date recognized by Excel.

Error Handling

If the serial_number is not a valid date, the DAY function will return the #VALUE! error. You can handle this using the IFERROR function:

=IFERROR(DAY(A1), "Invalid Date")

This will return “Invalid Date” if the date in cell A1 is not valid.

Summary

The DAY function is a simple yet useful tool in Excel for extracting the day of the month from a date. It’s helpful when you need to isolate the day component from a complete date, and it works with any valid Excel date format or serial number.