How to Use Excel DAY Function (Examples + Video)

Excel DAY Function (Examples + Video)

Excel Day Function

When to Use Excel DAY Function

Excel DAY function can be used when you want to get the day value (ranging between 1 to 31) from a specified date.

What it Returns

It returns a value between 0 and 31 depending on the date used as the input. For example, for the month of February, it will return a day value between 0 and 29.

Syntax

=DAY(serial_number)

Input Arguments

  • serial_number: It is the serial number of the date for which you want to get the day value. This could be the output from a function, a cell reference that contains a date value, or could be manually entered.

Additional Notes 

  • Apart from serial numbers, DAY function would also work with dates entered as:
    • A result from some other function.
    • A date entered as text in DAY function (in double quotes).
    • A date stored as text in a cell.
  • Excel can only handle dates starting from January 1, 1900 (for windows) and 1904 (for Mac).

Excel DAY Function – Examples

Here are four examples of using the Excel DAY function.

Example 1: Getting the Day value using a serial number

Excel DAY Function - Example 1

In the example above, Excel DAY function takes 42736 as the input. 42736 is the serial number of the date January 1, 2017. Since it represents the first day of the month, DATE function returns 1.

Example 2: Getting the DAY value using Date in a Cell

Excel DAY Function - Example 2

In the above example, DAY function takes the cell value from a cell and returns the day number of the month of that specified date. Note that if you use a date format that is not recognized by Excel, it returns a #VALUE! error.

Example 3: Getting the DAY Value of the current date

Excel DAY Function - Example 3

You can easily get current day value by using TODAY function as the input. TODAY function returns the current date, and the DAY function can use it to return the day value of that month.

In the example above, TODAY function returns 11-04-2016 (which is the current date while writing this tutorial). DAY function takes TODAY value as the input as returns 11.

Example 4: Getting the First Day of the Month

You can use Excel DAY function to get the first day of the month of the specified date.

Excel DAY Function - Example 4

In the example above, the day value for the date in A2 (15-03-2017) is 15. Since dates are stored as serial numbers in Excel, when we subtract the DAY value from this date and add 1 to it, it returns the first day of the month of the specified date.

Note that the dates in D2 and D3 are formatted as dates. It may happen that when you use this formula, you may get a serial number (for example, 42795 for March 1, 2017). You can then simply format it as a date.

The same logic can also be used to find the last day of the previous month. In that case, you can use the following formula (considering that the date is in cell A2):

=A2-DAY(A2)

Excel DAY Function – VIDEO

Related Excel Functions:

You May Also Like the Following Excel Tutorials: