How to Use DAY Function in Excel to Extract Day from Date

Use the DAY function in Excel to get the day number from a date. Enter =DAY(date) where date is a cell with a date or a date value, and it returns the day as a number from 1 to 31.

Syntax

The DAY function has a simple syntax:

• date: This is the date value or cell reference containing the date you want to extract the day from.

The function returns the day of the month as a number between 1 and 31.

excel

=DAY(date)

Example

This example shows how to extract the day from a date in cell A1. If A1 contains 2024-06-15, the formula =DAY(A1) returns 15.

excel

A1: 2024-06-15

In B1 enter:
=DAY(A1)

Output

15

Common Pitfalls

Common mistakes when using DAY:

• Using text that looks like a date but is not recognized as a date by Excel will cause errors or wrong results.
• Passing a number that is not a valid date serial number will give unexpected results.
• Remember that DAY only extracts the day number, not the month or year.

Wrong: =DAY("June 15") (text not recognized as date)
Right: =DAY(DATE(2024,6,15)) or reference a proper date cell.

excel

=DAY("June 15")  
=DAY(DATE(2024,6,15))

Output

Error or wrong result
15

Quick Reference

Function	Description	Example	Result
DAY(date)	Returns day number from date	=DAY("2024-06-15")	15
DAY(A1)	Returns day from date in cell A1	A1=2024-06-15, =DAY(A1)	15
DAY(DATE(year,month,day))	Extract day from constructed date	=DAY(DATE(2024,6,15))	15

Key Takeaways

Use =DAY(date) to get the day number from a date in Excel.

The date argument must be a valid date or cell with a date value.

DAY returns a number from 1 to 31 representing the day of the month.

Avoid using text that looks like a date but is not recognized by Excel.

Combine DAY with other date functions for more complex date handling.