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

Get FREE Advanced Excel Exercises with Solutions!

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


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)

Syntax of the DAY Function in Excel

  • 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.


How to Use DAY Function in Excel: 3 Ideal Examples

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.

3 Ideal Examples to Use DAY Function in Excel


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)

Inserting DAY Function Formula to Extract Number of Days as An Ideal Example to Use DAY Function in Excel

  • 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.


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.


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.


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.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo