The DAY function has a simple purpose when used by itself. It gives you the "day" component (i.e., a number from 1-31) of a date inside a cell. However, the DAY function allows us to play around with dates in our worksheets by using it with some logical arguments or other functions, as will see in the examples section.
But first, let's get some basics out of the way.
Syntax
The syntax of the DAY function is as follows:
=DAY(serial_number)Arguments:
serial_number – The DAY function has just one argument, where you will need to enter a date, either in the form of a serial number or cell reference.
Important Characteristics of the DAY function
- The DAY function extracts the day from a given date and returns it into a cell.
- The DAY function can be nested inside another function to relay the return (for example, in the DATE function).
- If the cell referenced in the
serial_numberargument does not contain a valid Excel date, the function will return a #VALUE! error.
Examples of the DAY function
Now let's have a look at some of the examples of the DAY function in Excel.
Example 1 – Plain-vanilla DAY formula
Let's use the DAY function to extract a day from a date without adding any other complexities. Here is the formula we will use:
=DAY(A2)
No rocket science, right? We have a date in cell A2, and we use the DAY function to extract the day from that date, which is '25.'
Example 2 – Finding the Number of Days in a Month with DAY function
The formula we will use here is pretty simple. It has a 2-step process and involves 2 functions: DAY and EOMONTH.
We know how the DAY function works. Let's talk about the EOMONTH function.
The EOMONTH function has 2 arguments. In the first argument, you must supply a date, and in the second argument, you must supply the number of months you want to add to that date.
The EOMONTH function returns the last date of the month, after adding the specified number of months to the given date. For example, if we supply the start date as "12/25/2001" and enter 1 in the second argument, the EOMONTH function will return 01/31/2002.
Alright, now that we have the EOMONTH function covered, let's look at the formula for finding the number of days in a given month:
=DAY(EOMONTH(A2, 0))
Since our start date is 12/25/2001, the EOMONTH will return 12/31/2001. Also, we know that the DAY function returns the day component of a date, and therefore, it will return '31,' i.e. the number of days in December.
Example 3 – Adding Days, Months, or Years with DAY and DATE function
Okay, so let's say we have a list of dates and a list of days/months/years that we want to add to those dates.
To accomplish this, we will nest the YEAR, MONTH & DAY functions inside the DATE function and add the relevant argument with the corresponding year, month or day to be added. So the formula would be :
=DATE(YEAR(A2) + D2, MONTH(A2) + C2, DAY(A2) + B2)Essentially, we are trying to add 0 years (from cell D2 to the first argument), 0 months (from cell C2 to the second argument), and 5 days (from cell B2 to the third argument) of the date function. When this formula is dragged down using the fill-handle it populates the correct formula against each row and gives us the suitable output.
Note that instead of putting a number for the years, months, or days to be added in a separate cell, we could just use the number in the formula after the '+' sign instead of referencing the cell.
Example 4 – Fetch the nth Weekday in a Given Month
Let's say we are trying to find out 2nd and 4th Saturdays in a month, for this we can make use of the DAY and WEEKDAY functions.
We'll prepare the formula as:
=A2 – DAY(A2) + 1 + n * 7 – WEEKDAY(A2 – DAY(A2) + 8 – 7)
Like we always do, let's dismantle this formula and understand what each component contributes to the final output.
Step 1
A2 is the start date. From A2, we will subtract the date itself using the DAY function and then add 1.
Why?
Well, all we want to do in this part of the formula is bring the formula to the 1st day of the month. In our example, cell A2 contains the date 12/22/2001. Removing 22 days using the DAY function, and adding 1, gives us 12/01/2001.
Step 2
Great, so the next component of the formula is n * 7. 'n' here is the occurrence of the day in a given month that we want to compute. In our example, we want the 2nd and 4th Saturday of the given months, so we will set our n to '2' and '4'. In effect, this computation will take us 'n' number of weeks into a given month.
We will now continue our discussion with the list of 2nd Saturdays, but the logic remains the same for both lists.
In step 1, we navigated to the 1st date of the month. Now, what should we add to this to reach the last day of the nth week? Just the number of weeks multiplied by the number of days in a week, right? So, we will add 14 days (2 * 7) for our list of 2nd Saturdays.
This brings our formula to 12/15/2001.
Step 3
The final step. Now that we have navigated n number of weeks into a given month, let's navigate to the specific day we want.
While 12/15/2001 (as computed in Step 2) also happens to be a Saturday, it is the 3rd Saturday of the month.
So, we will use the WEEKDAY function to backtrack to the intended day. WEEKDAY returns a number from 1 to 7 in which 1 represents a Sunday, and 7 represents a Saturday.
In our example, the WEEKDAY function returns a '7.'
Why does WEEKDAY return 7?
The '7' represents the weekday as on the first date in a given month since, within the WEEKDAY function, we navigate to the first day using the DAY function and then add 1.
So, let's subtract 7 from 12/15/2001 to get our final output.
We get 12/08/2001, which is the 2nd Saturday; the first Saturday being 12/01/2001.
Example 5 – Get the First Day of the Month with DAY function
I sort of gave this away in the previous example, but let's quickly recap through how we can compute the first day of the month using the DAY function.
We will use the following formula:
=A2 – DAY(A2) + 1
Does this look familiar? We used this formula in our previous example, too.
A2 is our start day. When we use the DAY function and reference cell A2 in the argument, we are essentially asking it to fetch the day component of the date.
In our example, our start date is December 25, and the DAY function returns 25. Subtracting 25 days from December 25 will give us November 30, so we add 1 and get our final output as 12/01/2001, i.e. the first day of the month.
That's a wrap on DAY function. When you are done playing around with the DAY function and ace these formulas, we will have another exciting Excel function for you. Until then, keep crunching those numbers.
About Supriya