The DAY function is a simple date function that extracts just the day from a given time. If you work with times but only care about the day, this function lets you get rid of everything else. Typically you will find this function combined with others to return a number for further calculation.
This function is aware of the number of days in each month. Therefore, it knows that January, for example, has 31 days but that the 31st of February is not a valid value as there are not 31 days in February.
Before reading the examples below, ensure you know how dates and times work as a large part of using date functions correctly is understanding what makes up a valid date. If you do not provide a valid date, the formula will error.
Contents
Purpose
The DAY function returns the day’s value as a number from any given unit of time.
Syntax
=DAY(date)
TIMEDIF – Google Sheets Add-On
Calculate any duration in:
- Years
- Months
- Weeks
- Days
- Hours
- Minutes
- Seconds
- Milliseconds
Common Errors
#VALUE! – An invalid date was passed to the function such as "1/32/2022". The month of January does not have 32 days.
#ERROR! – Formula parse error. The input to the function could not be used such as a date with too many slashes. – 1//21/2222.
#NUM! – Result was not a number. The number is too large to resolve to a date. – (548748511).
The output is an unexpected format such as 12:00 AM. – This means the formatting is incorrect, not the underlying number. Change the formatting to Number.
Examples
Example 1 – Plain and Simple
Below are a few simple examples with their respective inputs and outputs. As you can see, using the function is relatively straightforward.
| Formula | Description | Result |
=DAY("1/2016") | Extract the day value from 1/2016. | 1 |
=DAY("1/31/2016") | Extract the day value from 1/31/2016. | 31 |
=DAY("1") | Extract the day value from 1. | 31 |
Tip: The DAY function shows its eccentricity when converting the number 1. See example 9 in Example 2 below. It helps to keep in mind how dates and times work to make a little sense out of this.
Example 2 – Separating the Values of a Date and Time
Different date values examined using various functions.
| Time value | Tickmark | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | Formatted as number |
|---|---|---|---|---|---|---|---|---|
| 7/30/2016 | 1 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
| 7-30-2016 | 2 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
| 6/1/2017 2:32 PM | 3 | 2017 | 6 | 1 | 14 | 32 | 0 | 42,887.61 |
| July 31, 2016 | 4 | 2016 | 7 | 31 | 0 | 0 | 0 | 42,582.00 |
| 12:30 PM | 5 | 1899 | 12 | 30 | 12 | 30 | 0 | 0.52 |
| 4/11/2017 11:24:14 | 6 | 2017 | 4 | 11 | 11 | 24 | 14 | 42,836.48 |
| 4/11/2017 | 7 | 2017 | 4 | 11 | 0 | 0 | 0 | 42,836.00 |
| 8 | 1899 | 12 | 30 | 0 | 0 | 0 | ||
| 1 | 9 | 1899 | 12 | 31 | 0 | 0 | 0 | 1.00 |
| 0.25 | 10 | 1899 | 12 | 30 | 6 | 0 | 0 | 0.25 |
| 43000 | 11 | 2017 | 9 | 22 | 0 | 0 | 0 | 43,000.00 |
- 1 A random date typed in using slashes.
- 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
- 3 Typed a random date in and added a time. Now the function extracts the hours and minutes.
- 4 Typed in a date but spelled out. Writing out the date also works, but it won’t work if you write the day as “31st” instead of 31.
- 5 If you only type in a time, the “zero” date is 12/30/1899.
- 6 Entered with the NOW function. The output will constantly change in the linked Google Sheet.
- 7 Entered with the TODAY function. The output will change daily in the linked Google Sheet.
- 8 A blank cell is December 30, 1899. Similar to tickmark 5.
- 9 Typing a 1 will increment the value in number 10 above by 1 day. In other words, “1=12/31/1899”. It is just a matter of how it is displayed, which can be controlled by going to the Format menu and choosing Number.
- 10 Just a decimal with no whole number increments the value by hours, minutes, and seconds instead of days.
- 11 To get near the current date, you need to start with 43,000 days away from 12/30/1899.
Live Examples in Sheets
Go to this spreadsheet for examples of date functions that you can study and use anywhere you want.