How to Use the DAY function in Excel

Get the day of a date in numeric form

By
Tricia Goss
Tricia Goss
Tricia Goss
Writer
  • Pima College
Tricia Goss has been a writer and editor for 10+ years. She's written tips and tutorials for Microsoft Office applications and other sites.
lifewire's editorial guidelines
Updated on June 15, 2022

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.

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.

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.

  1. 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.

    The location of the Date tool in Microsoft Excel.

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

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

    The location of the Formulas tab on the Excel Ribbon.

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

    The Excel Date & Time Functions location.

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

    The location of the DAY function in the Date & Time Functions list.

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

    The DAY function arguments showing the location of the Serial_number.

  7. 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.

  1. Select the column containing the dates.

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

    Screenshot of Format Cells in right-click menu.

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

    The Number tab on the Format Cells dialog.

  4. 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.

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 Excel DAY function using within a formula.

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.

Was this page helpful?