Excel DAY/DAYS Functions

Extract Days from Dates and Subtract Dates

Extract the day of the week from a date with the Excel DAY Function
Extract the Day of the Week from a Date with the Excel DAY Function. (Ted French)

The DAY function in Excel can be used to extract and display the month portion of a date that has been entered into the function.

The function's output is returned as an integer ranging from 1 to 31.

A related function is the DAYS function that can be used to find the number of days between two dates that occur in the same week or month using a subtraction formula as shown in row 9 of the example in the image above.

Pre Excel 2013

The DAYS function was first introduced in Excel 2013. For earlier versions of the program, use the DAY function in a subtraction formula to find the number of days between two dates as shown in row eight above.

Serial Numbers

Excel stores dates as sequential numbers—or serial numbers—so they can be used in calculations. Each day the number increases by one. Partial days are entered as fractions of a day, such as 0.25 for one quarter of a day (six hours) and 0.5 for half a day (12 hours).

For Windows versions of Excel, by default:

  •     January 1, 1900 = serial number 1;
  •     January 1, 2016 = 42370 (since it is 42,370 days after January 1, 1900);
  •     12 o'clock noon on January 1, 2016 is 42370.50.

DAY/DAYS Functions Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the DAY function is:

= DAY ( Serial_number )

Serial_number - (required) a number representing the date from which the day is extracted.

This number can be:

  • a serial number representing a date—row 4 in the example;
  • a cell reference to the worksheet location of a date—row 5 in the example;
  • a date entered as the function's argument using the DATE function—row 6 in the example;
  • the current date entered as the function's argument using the TODAY or NOW functions—row 6 in the example.

Note: If a bogus date is entered into the function—such as February 29 for a non-leap year—the function will adjust the output to the correct day of the following month as shown in row 7 of the image where the output for the date February 29, 2017 is one—for March 1, 2017.

The syntax for the DAYS function is:

DAYS(End_date, Start_date)

End_date, Start_date  - (required) these are the two dates used to calculate the number of days.


  • If date arguments are numeric values that fall outside the range of valid dates, such as August 32, 2016, DAYS will return the #NUM! error value.
  • If either date argument is entered as a text string that cannot be parsed as valid a valid date, DAYS returns the #VALUE! error value.

Excel WEEKDAY Function Example

Rows three to nine in the example above display a variety of uses for the DAY and DAYS functions.

Also included in row 10 is a formula combining the WEEKDAY function with the CHOOSE function in a formula to return the name of the day from the date located in cell B1.

The DAY function cannot be used in the formula to find the name because there are possibly 31 results for the function, but only seven days in a week entered into the CHOOSE function.

The WEEKDAY function, on the other hand, only returns a number between one and seven, which can then be fed into the CHOOSE function to find the name of the day.

How the formula works is:

  1. The WEEKDAY function extracts the number of the day from the date in cell B1;
  2. The CHOOSE function returns the day name from the list of names entered as the Value argument for that function.

As shown in cell B10, the final formula looks like this:


Below are listed the steps used to enter the formula into the worksheet cell.

Entering the CHOOSE/WEEKDAY Function

Options for entering the function and its arguments include:

  1. Typing the complete function shown above into a worksheet cell;
  2. Selecting the function and its arguments using the CHOOSE function dialog box.

Although it is possible to just type the complete function in manually, many people find it easier to use the dialog box which looks after entering the correct syntax for the function, such as the quotation marks surrounding each day name and the comma separators between them.

Since the WEEKDAY function is nested inside CHOOSE, the CHOOSE function dialog box is used and WEEKDAY is entered as the Index_num argument.

This example returns the full name for each day of the week. To have the formula return the short form, such as Tues. rather than Tuesday, enter the short forms for the Value arguments in the steps below.

The steps for entering the formula are:

  1. Click on the cell where the formula results will be displayed, such as cell A10;
  2. Click on the Formulas tab of the ribbon menu;
  3. Choose Lookup and Reference from the ribbon to open the function drop down list;
  4. Click on CHOOSE in the list to bring up the function's dialog box;
  5. In the dialog box, click on the Index_num line;
  6. Type WEEKDAY(B1) on this line of the dialog box;
  7. Click on the Value1 line in the dialog box;
  8. Type Sunday on this line;
  9. Click on the Value2 line;
  10. Type Monday;
  11. Continue entering the names for each day of the week on separate lines in the dialog box;
  12. When all days have been entered, click OK to complete the function and close the dialog box;
  13. The name Thursday should appear in the worksheet cell where the formula is located;
  14. If you click on cell A10 the complete function appears in the formula bar above the worksheet.