Updated July 3, 2023
DAY in Excel
The Day function in Excel looks at a given date and figures out what day of the month it is. It then gives you a number from 1 to 31, representing the day. For example, if you have a date “April 16, 2023” in cell B1 and use the formula =DAY (B1), you will get the result as 16.
Excel has categorized the DAY function under the Date/Time function, a built-in function in Excel.
DAY Formula in Excel
The formula/syntax is:
where,
Date_value/serial_number – You must enter an Excel date with a serial number format.
To access the Day function in Excel:
1. Click on the Formulas
2. Select the ‘Date & Time’ option.
3. Select the DAY function from the drop-down list.
How to Use DAY Function in Excel?
Example #1
Let’s say you have a list of dates in column A and want to extract only the day in another column, say column C.
Here are the steps:
1. First, download the DAY Function Template. This template has all examples used in this article.
2. In cell C5, type the formula “=DAY(A5)” and press enter.
3. This will extract the day (28) from the date.
4. Drag and drop the formula from cell C5 to the remaining cells in column C to get the day for all the dates in column A.
5. Your result will be displayed in column C.
Example #2
Suppose we have the below dates, and we need to add a given number of years to these dates. Let us use the DAY function to accomplish this:
We will enter this formula in cell C13: =DATE(YEAR(A13)+B13,MONTH(A13),DAY(A13))
The formula provides the result as shown in the image below:
Drag & drop this formula to the remaining cells, and it gives the below result:
Explanation:
- The functions- YEAR, MONTH, and DAY that we used in the above formula retrieve the below data components:
For cell A13,
Year= 2019
Month= 3 (March)
Date= 16
Example #3
Suppose we want to find the first day of the month on a given date. For this, we will use the DAY function.
We will enter this formula in cell C24: =A24-DAY(A24)+1
Excel provides the result as shown in the image below:
Drag & drop this formula for the rest values, and Excel provides the below result:
Explanation:
- The DAY function returns the day value for a date. The day value for the date 11/23/2020(cell A24) is 23.
- Excel stores the dates as serial numbers. So, we can subtract the value of 23 and then add 1 to obtain the date value for 11/01/2020.
Example #4
We can calculate the number of days for a month on a given date with the help of the DAY function.
We will this formula: =DAY(EOMONTH(A36,0))
Drag & drop the formula to the remaining cells, which gives the results shown below:
Common Errors and Fixes for Day in Excel
- #VALUE! – Excel gives a value error if we provide a date in the incorrect format, as shown below
To fix this error, follow these steps,
Step 1: Right-click the date, and choose Format Cells in the context menu
A Format Cells window pops up,
Step 2: Under the Format cells window, click Date under the Number tab, choose the desired date format from the list under the Type section, and click OK.
The entire process is illustrated in the image below.
Things to Remember
- The date argument must be a valid Excel date.
- Sometimes the DAY function provides a date- 01/01/1900, which looks like a date, while it needs to be an integer.
- To fix the above issue, go to the Number group in the Home tab and select Number from the various options. The process is illustrated in the following image.
In the below image, select the Number option under General Option.
Frequently Asked Questions (FAQs)
Q1. How do I use the day formula in Google Sheets?
Answer: To use the day formula in Google Sheets, follow these steps:
Step 1: Place the cursor in the cell where you want to display the Day and enter the formula =DAY(date)
Step 2: Hit Enter key to get the result
Alternatively,
You can insert the Day function by following these steps:
Insert > Function > Date > Day
Q2. How do I add a day in Google Sheets?
Answer: To add a given number of days to a date in Google Sheets, perform an addition of the two cells, i.e., use the formula = date + number of days.
Recommended Articles
The above article has been a guide to DAY in Excel. Here we discuss the DAY function in Excel and the way of using the DAY function along with downloadable Excel templates. EDUCBA recommends the following useful functions in Excel –
