Summary

If you need to extract the day from a date, you can use the DAY function. The date must be in a form that Excel recognizes as a valid date. In the example shown, the formula in cell B5 is:

=DAY(B5)

Generic formula

=DAY(date)

Explanation 

The DAY function takes just one argument, the date from which you want to extract the day. In the example, the formula is:

=DAY(B5)

B5 contains a date value for January 5, 2016. The DAY function returns the number 5 representing the day component of the date.

Note that you can use DAY to extract the day from a day entered as text:

=DAY("1/5/2016")

But this can produce unpredictable results on computers using different regional date settings. In general it's better (and more flexible) to supply an address to a cell that already contains a valid date value as the argument for DAY.

Related formulas

Excel formula: Get day name from date

Get day name from date

In this example, the goal is to get the day name (i.e. Monday, Tuesday, Wednesday, etc.) from a given date. There are several ways to go about this in Excel, depending on your needs. This article explains three approaches: Display date with a custom number format Convert date to day name with TEXT...
Excel formula: Get month from date

Get month from date

The MONTH function takes just one argument, the date from which to extract the month. In the example shown, the formula is: =MONTH(B4) where B4 contains the dateJanuary 5, 2016. The MONTH function returns the number 1 representing the month( January) of the date. Note that you can use MONTH to...
Excel formula: Get year from date

Get year from date

The YEAR function takes just one argument, the date from which you want to extract the year. In the example, the formula is: =YEAR(B4) B4 contains a date value for January 5, 2016. The YEAR function returns the number 2016 representing the year of the date. Note that you can use YEAR to extract the...

Related functions

Excel DAY function

DAY Function

The Excel DAY function returns the day of the month as a number between 1 to 31 from 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...
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.

Formulas

Functions

Training