Excel 2003/2007/2010/2013: How to extract the "Month", Day or Year in a DATE entry in a cell?

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

Suppose you are working in an Excel sheet containing cells with dates. Depending on your actual requirement, one of the things that you might want to do is to extract the day, month, or even the year in a date entry in an Excel cell.

extracting day, month or year in DATE in Excel

For instance, your cell A1 contains the date June 7, 2013. It could have been entered as 6/7/2013 but the cell was format to show the date in a long pattern. But as long as Excel recognizes the data as a DATE, then it stores the date in the form 6/7/2013. Here is a sample entry in Excel where both cells contain the same date 6/7/2013 but B2 is formatted to show the long format.

date long format

How can I extract the DAY in the date?

In the sample above, you can use the DAY() function in a formula to extract the day in the date. Thus, =Day(B2) above gives 7.

Excel DAY formula

How can I extract the month?

In the same manner, you can extract the month in cell B2 using the Month() function. Thus, =Month(B2) above gives 6 which represents June.

Excel month function

What if I want to display the month's name instead of number?

In the sample above, there is a way to display "June" instead of the number 6. This is by using the Text() function. The Text() function will convert a given value to text in a given text format. To display "June" instead of 6, the formula should be =Text(B2,"mmmm").

Excel long month

Please note that the TEXT() function not only works with a DATE data in the cell. It can convert numbers to a particular format.

How can I extract the year in the date?

In similar manner like the Day() and Month() functions, Excel has a Year() function to extract the year in a date entry. Thus, the formula =Year(B2) gives 2013.

Excel year formula

 

MS Office Software: