How to Use DAY Function in Excel (With 3 Examples)

Excel provides several functions to maximize and speed up your productivity. Today we are going to show you the usage of the 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.

Excel Sheet - Excel Day Function

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.

DAY Function in Excel

1. Basics of 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)

DAY - Excel Day Function

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. 

2. Application of the 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. 

Extract day - Excel Day Function

Since this is the basic operation of DAY all you need to insert your date within the function.

Extract day formula - Excel Day 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.

Extract day result - Excel Day Function

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.

Autofill - Excel Day Function

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.

First day of month - Excel Day 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 

First date of month - Excel Day Function

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.

Result - Excel Day Function

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.

AutoFill - Excel Day Function

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.

Date after years - Excel Day Function

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))

Formula date after years- Excel Day Function

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.

date after Years- Excel Day Function

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.

AutoFill - Excel Day Function

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.

Error presentation - Excel Day Function

Your result can appear like this.

You need to Explore the Number section from the Home tab. Select General format there.

Reduce error - Excel Day Function

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.

Value Error - Excel Day Function

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

Value Error 2 - Excel Day Function

And still, the DAY function cannot produce the appropriate output. So be careful while inserting dates for using DAY. 

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.


Further Readings:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo