Summary

The Excel DAY function returns the day of the month as a number between 1 and 31 based on a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the DATE function.

Purpose 

Get the day as a number (1-31) from a date

Return value 

A number (1-31) representing the day component in a date.

Syntax

=DAY(date)
  • date - A valid Excel date.

How to use 

The DAY function returns the day component of a given date as a number between 1 to 31 from a given date. For example, with the date January 15, 2019 in cell A1:

=DAY(A1) // returns 15

You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value into another function, like the DATE function. For example, to change the year of a date in cell A1 to 2020, but leave the month and day as-is, you can use a formula like this:

=DATE(2020,MONTH(A1),DAY(A1))

See below for more examples of formulas that use the DAY function.

Note: in Excel's date system, dates are serial numbers. January 1, 1900, is number 1 and later dates are larger numbers. To display date values in a human-readable date format, apply a number format of your choice.

Notes

  • The date argument must be a valid Excel date. 
  • The DAY function returns #VALUE! if the date is not valid.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.