Shakil Ahmed
Excel provides several functions to maximize and speed up your productivity. Today we are going to show you the usage of the Excel DAY function. For conducting the session, we are using Excel 2019, you can use yours (at least Excel 2003).
Before diving into the big picture, let’s get to know about today’s workbook.
We have a simple table with a few random people with their dates of birth. We will use the date column as the base of our operations.
Using this table we will try to provide you information regarding the DAY function.
Note that, this is a basic dataset to keep things simple, practically you may encounter a much larger and complex data set.
Practice Workbook
You are welcome to download the practice workbook from the link below.
Excel DAY Function
1. Basics of Excel DAY Function
The DAY function returns the day of a date as a number between 1 to 31. This function is used for extracting a day number from a date.
The syntax for the DAY Function is as follows
DAY(serial_number)
serial_number: A valid Excel date. You can provide the date value directly or use the cell reference.
Excel version is workable from Excel 2003.
Read More: How to Use the Excel DAYS Function with a Practical Example
2. Application of the Excel DAY Function
Depending on the circumstances you may use the DAY function in various ways. To clarify different usages, here are some honorable mentions.
I. Extract Day Number
It is obvious that you can extract the day number using the DAY function. To show you examples we have introduced two columns; Formula and Day.
Since this is the basic operation of DAY all you need to insert your date within the function.
From the syntax, you know that within the DAY function we insert an argument serial_num. Here for simplicity, we have used the date as our function’s argument.
Write the formula in Excel.
We have inserted the first date from our table. We are using Cell Reference. It is a good practice to perform operations using the Cell Reference.
The DAY function provided the day of the month from the given date.
Do the same for the rest of the rows or exercise Excel AutoFill.
II. Find the First Day of the Month
One of the common uses of the DAY function is to find the first day of the month. From a given date you can find the first day of the month using this function.
We have introduced a new column Month First Day to show you examples.
All we need to do is subtract the day from the date and then add 1.
The generic formula will be something like this
date - DAY(date) + 1 
Do you understand the formula? Let’s help you a bit, subtracting the day value from the date we find 0 at the position of the day in the date.
And then we add 1 since all the months starting from 1.
Write the formula in Excel.
Here we have found the starting date of the month.
Do the same for the rest of the rows or you can use the AutoFill feature.
III. Add Any particular Year
Sometimes you may want to find the date after some years from any date. Let’s see how you can do that.
To show you the procedure we have inserted an additional column into our dataset.
We need to use a formula that consists of DATE, YEAR, and MONTH.
The YEAR function returns the year component of a date as a 4-digit number.
YEAR(serial_number)The MONTH function extracts the month from a given date as a number between 1 to 12.
MONTH (serial_number)From the syntax, you can understand that YEAR, MONTH and DAY are similar. They return the respective component from a date.
The DATE function creates a valid date from the individual year, month, and day components.
DATE (year, month, day)We will provide the year, month, and day in the DATE using the YEAR, MONTH, and DAY function respectively.
Let’s see the formula:
DATE(YEAR(date)+year_add,MONTH(date),DAY(date))
Here MONTH and DAY extract the month and day number respectively. Simple as you like.
YEAR extracts the year from the date and then we add the years to this derived year number.
Write the formula in Excel.
Notice here, everything remains the same only the year changed. We have found the date after 18 years.
Do the same for the rest of the values.
Read More: How to use HOUR Function in Excel (5 Easy Examples)
Similar Readings
- How to Use Excel MONTH Function (6 Examples)
- Use YEAR Function in Excel (5 Examples)
- How to Use DAYS Function in Excel (7 Examples)
- Remove Time from Date in Excel (6 Approaches)
- How to Auto-Update Current Time in Excel (With Formula and VBA)
3. Common Problems
You may encounter several problems while dealing with the DAY function.
Sometimes you may find the result of DAY in another format apart from an integer. The most common format is 01/01/1990, where the first 01 will be changed depending on your result.
Your result can appear like this.
You need to Explore the Number section from the Home tab. Select General format there.
Another common problem is a mismatch in date format. Doesn’t matter how you represent but in most of the region, you need to insert date as dd/mm/yyyy.
If you insert a date in any other format, Excel may not consider that as a valid date.
Here we have changed the date to 12-31-1999 (mm/dd/yyyy) and the DAY function cannot detect the value.
See another example, where we have inserted date as yyyy/dd/mm
And still, the DAY function cannot produce the appropriate output. So be careful while inserting dates for using DAY.
Related Content: How to Use DATEVALUE Function in Excel (6 Suitable Examples)
Conclusion
That’s all for the session. The DAY function may play a vital role in different circumstances. From executing basic operations like extracting day numbers to different nested operations can be performed by DAY. We have tried to show you some of the usages. Hope you will find this helpful.
Feel free to comment if anything seems difficult to understand. Use the DAY function carefully. Let us know any of your DAY function-related scenarios where you have stuck, we are ready to help.
