This article explains how to use the DAY function in Microsoft Excel to return a date as a serial number using an integer between 1 and 31.

What to Know

  • Syntax for function: DAY(serial_number).Enter date in spreadsheet > select cell > select Formulas > Date & Time > DAY > Serial_number > choose cell.If not working, select date column > right-click and select Format Cells > Number tab > Number > OK.

How to Use the DAY function in Excel 

The syntax for the DAY function is DAY(serial_number).

The only argument for the DAY function is serial_number, which is required. This serial number field refers to the date of the day you are trying to find.

Dates are serial numbers in Excel’s internal system. Beginning with January 1, 1900 (which is number 1), each serial number is assigned in ascending numerical order. For example, January 1, 2008 is serial number 39448 because it is 39447 days after January 1, 1900.

Excel uses the serial number entered to determine which day of the month the date falls on. The day is returned as an integer ranging from 1 to 31.

  • Enter a date in an Excel spreadsheet using the DATE function. You can also use dates appearing as the result of other formulas or functions.
  • If dates are entered as text, the DAY function might not work as it should.
  • Select the cell where you want the day of the date to appear.
  • Select Formulas. In Excel Online, select the Insert Function button next to the formula bar to open the Insert Function dialog box.
  • Select Date & Time to open the Function drop-down list. In Excel Online, choose Date & Time in the Pick a Category list.
  • Select DAY in the list to bring up the function’s dialog box.
  • Make sure the Serial_number field is selected and then choose the cell containing the date you want to use.
  • Choose OK to apply the function and view the number representing the day of the date.

DAY Function Not Working

If your results are not appearing as numbers but rather as dates, it is likely a simple formatting issue. Problems could occur if the numbers are formatted as text rather than dates. Checking the format for the cells containing the serial number portion of the DAY function syntax (and then changing the format, if necessary) is the most likely way to resolve an error or incorrect result.

Enter a date in an Excel spreadsheet using the DATE function. You can also use dates appearing as the result of other formulas or functions.

If dates are entered as text, the DAY function might not work as it should.

Select the cell where you want the day of the date to appear.

Select Formulas. In Excel Online, select the Insert Function button next to the formula bar to open the Insert Function dialog box.

Select Date & Time to open the Function drop-down list. In Excel Online, choose Date & Time in the Pick a Category list. 

Select DAY in the list to bring up the function’s dialog box.

Make sure the Serial_number field is selected and then choose the cell containing the date you want to use.

Choose OK to apply the function and view the number representing the day of the date.

  • Select the column containing the dates.
  • Right-click anywhere on the selected cells and select Format Cells.
  • Make sure the Number tab is selected and choose Number in the Category list.
  • Select OK to apply the changes and close the dialog. Your results will appear as integers instead of dates.

When to Use the DAY Function in Excel

The DAY function is useful for financial analysis, primarily in a business setting. For instance, a retail organization might want to determine what day of the month has the highest number of customers or when most shipments arrive.

Select the column containing the dates.

Right-click anywhere on the selected cells and select Format Cells.

Make sure the Number tab is selected and choose Number in the Category list.

Select OK to apply the changes and close the dialog. Your results will appear as integers instead of dates.

You can also extract a value using the DAY function inside of a larger formula. For instance, in this sample worksheet, the DAY function helps determine how many days are in the listed months.

The formula entered in cell G3 is

=DAY(EOMONTH(F3,0))

The DAY function provides the day component and EOMONTH (end of month) gives you the last day of the month for that date. The syntax for EOMONTH is EOMONTH(start_date, months). So the start date argument is the DAY entry and the number of months is 0, resulting in a list showing the number of days in the months presented.

Be careful not to confuse the DAY function with the DAYS function. The DAYS function, which uses the syntax DAYS(end_date, start_date) returns the number of days between a start date and an end date.

Get the Latest Tech News Delivered Every Day