Shakil Ahmed
Excel provides several functions to maximize and speed up your productivity. One such function is the DAY function of Excel. Today I am going to show you the usage of the DAY function in Excel. We are using Microsoft 365 for conducting the session. You can use yours (at least Excel 2003).
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
Introduction to DAY Function
- Summary
The DAY function returns the day of a date as a number between 1 and 31. This function is used for extracting a day number from a date.
- Syntax
The syntax of the DAY function is as follows
=DAY(serial_number)
- Argument
| Argument | Required or Optional | Value |
|---|---|---|
| serial_number | Required | A valid Excel date. You can provide the date value directly or use the cell reference. |
- Return
The function will return the day of a date based on the input cell reference.
3 Ideal Examples to Use DAY Function in Excel
Depending on the circumstances, you may use the DAY function in various ways. In this article, you will find three ideal examples of how to use the DAY function in Excel. Each of these examples will represent a different situation.
To demonstrate these situations, I will use the following sample data set. Here, I have a simple table with a few random people with their dates of birth. I will use the date column as the basis of our operations.
1. Extract Number of Days from a Given Value
It is obvious that you can extract the day number using the DAY function. For that, you have to insert a cell containing a specific date into the function as a reference. See the following steps for a better understanding.
Steps:
- First of all, in order to extract the number of days from the value of cell C5, insert the following formula in cell D5.
=DAY(C5)
- Secondly, press Enter and the formula will show the number of days from a specific date. In this example, it is 31.
- Correspondingly, to get the number of days for the following cells as well use AutoFill.
Read More: How to Use TODAY Function in Excel (6 Easy Examples)
2. Find First Day of Month
One of the common uses of the DAY function is to find the first day of the month. You can find the first day of the month from a given date using this function. Now, follow the below-given steps to execute this procedure.
Steps:
- Firstly, to find the first day of a month from a given date, type the following formula in cell D5.
=C5-DAY(C5)+1- Here, cell C5 contains a particular date and you need to subtract the day from the date and then add 1.
- Consequently, by subtracting the day value from the date you will find 0 at the position of the day in the date. And then you need to add 1 since all the months start from 1.
- Secondly, press Enter to see the result for cell D5.
- After that, drag the Fill Handle in order to get the desired results for the entire column.
Read More: How to Use DAYS Function in Excel (7 Examples)
Similar Readings
- Remove Time from Date in Excel (6 Approaches)
- How to Auto-Update Current Time in Excel (With Formula and VBA)
- Use TIME Function in Excel (8 Suitable Examples)
- How to Use DATEDIF Function in Excel (2 Ideal Examples)
3. Add Any Particular Year
Additionally, sometimes you may want to find the date some years after the original date. What would be the formula in order to do that? In order to perform this task, I will use a combination formula that will include the DATE, MONTH, DAY, and YEAR functions of Excel. Let’s see how you can do that in the following steps.
Steps:
- In the beginning, look at the following image, where, you will find some years to be added with the original date of birth.
- Secondly, use the following combination formula in cell E5.
=DATE(YEAR(C5)+D5,MONTH(C5),DAY(C5))
Formula Breakdown
=DATE(YEAR(C5)+D5,MONTH(C5),DAY(C5))
- ( MONTH(C5),DAY(C5)): Firstly, the MONTH and the DAY function will extract the month and day number respectively of the given date of cell C5.
- YEAR(C5)+D5,MONTH(C5),DAY(C5): Secondly, the YEAR function extracts the year from the date and then I add the years to this derived year number.
- DATE(YEAR(C5)+D5,MONTH(C5),DAY(C5)): Finally, the DATE function will show the desired date value after adding particular years.
- Finally, to see the desired result after using the formula, hit Enter, and after that use AutoFill.
Read More: How to Use the Excel DAYS Function with a Practical Example
Things to Remember
- Sometimes you may find the result of the DAY function in another format apart from an integer. To solve this, you need to explore the Number section from the Home tab and select the format as General there.
- Yet, Another common problem is a mismatch in date format. Doesn’t matter how you represent but in most of the regions, you need to insert the date as dd/mm/yyyy. If you insert a date in any other format, Excel may not consider that as a valid date.
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to understand how to use the DAY function in Excel. If so, Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.
Related Articles
- How to Use WORKDAY.INTL Function in Excel (A Complete Guideline)
- Use WEEKDAY Function in Excel (with 8 Examples)
- How to Use the NOW Function in Excel (8 Suitable Examples)
- Use SECOND Function in Excel (3 Suitable Examples)
- How to Use MINUTE Function in Excel (6 Suitable Examples)
- Use DATEVALUE Function in Excel (6 Suitable Examples)
