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.