DAY Function

It returns the day component as a number from a given date in Excel.

Author: Josh Pupkin
Josh Pupkin
Josh Pupkin
Private Equity | Investment Banking

Josh has extensive experience private equity, business development, and investment banking. Josh started his career working as an investment banking analyst for Barclays before transitioning to a private equity role Neuberger Berman. Currently, Josh is an Associate in the Strategic Finance Group of Accordion Partners, a management consulting firm which advises on, executes, and implements value creation initiatives and 100 day plans for Private Equity-backed companies and their financial sponsors.

Josh graduated Magna Cum Laude from the University of Maryland, College Park with a Bachelor of Science in Finance and is currently an MBA candidate at Duke University Fuqua School of Business with a concentration in Corporate Strategy.

Reviewed By: Christopher Haynes
Christopher Haynes
Christopher Haynes
Asset Management | Investment Banking

Chris currently works as an investment associate with Ascension Ventures, a strategic healthcare venture fund that invests on behalf of thirteen of the nation's leading health systems with $88 billion in combined operating revenue. Previously, Chris served as an investment analyst with New Holland Capital, a hedge fund-of-funds asset management firm with $20 billion under management, and as an investment banking analyst in SunTrust Robinson Humphrey's Financial Sponsor Group.

Chris graduated Magna Cum Laude from the University of Florida with a Bachelor of Arts in Economics and earned a Master of Finance (MSF) from the Olin School of Business at Washington University in St. Louis.

Last Updated:December 9, 2023

What is the DAY Function?

The DAY function returns the day component as a number from a given date in Excel. The value returned can range between 1 to 31, corresponding to different days in a month, including leap years, depending on the date supplied to the function.

Dates are often used with critical data or information as a timestamp to understand the day the ‘event’ occurred. If we can extract the day component, we can potentially group all those events for a single day and make an in-depth analysis of those events.

For example, a bakery can reconcile daily transactions and even understand their best-selling and average-selling product from the menu.

A hair salon can determine the busiest day of the week for them by looking at all the appointments made in a particular week.

Many different trends can be analyzed with the help of dates that cannot be studied in-depth in monthly or yearly data.

This article will show the DAY function, its uses, and some examples.

Key Takeaways

  • The DAY function extracts the day component from a given date to return a number that ranges from 1 to 31.
  • The function returns a #VALUE! Error if we do not input a valid date.
  • The function accepts a single argument in the form of a serial number corresponding to a date, a date value, or even a formula that returns a date.
  • You can use the MONTH function to extract the month component from a given date. The value returned can be between 1 to 12, corresponding to the twelve months from January to December.
  • The YEAR function extracts the year from a given date. The extracted year can lie between 1900 .x 9999 since Excel does not store dates before 1st January 1900 as serial numbers.
  • The DAYS function looks similar to the DAY function; however, it is used to find the difference between two dates in terms of days.

Understanding DAY function

The DAY is categorized as a Date and Time function that returns the day from the given date in Excel.

It doesn’t matter whether the date is in dd-mm-yyyy, mm-dd-yyyy, or any other format. If you use the DAY function, it will effectively extract the same component from a given date.

For example, if you have the date as 31st July 2022 and use the function to reference the cell containing the date, you will get the result as 31.

Input

Similarly, if the date is 18th August 2022, the result would equal 18.

Result

You must be wondering if extracting day is possible. Can we say the same about the month and year components?

You are right; we also have the MONTH and YEAR function that extracts the corresponding counterparts from a given date.

We might cover both of those functions at the end of the article, but for now, we will focus on the DAY function.

The syntax for the function is

=DAY(serial_number)

where

  • serial_number - (required) a serial number corresponding to a date, a cell containing a date, or a formula that returns the date, which can be used to extract the day.

Note

All the dates beginning from 1st January 1900 are stored as serial numbers in Excel. Thus, 1st January 1900 will be stored as 1, 2nd January 1900 will be stored as 2, and so on.

If you input dates before this lower limit, the date will be stored as a text value rather than a date. The date would still be visible; however, using it along with other date and time functions will return the #VALUE! Error.

How to use the DAY function?

The date can be hardcoded in the formula, or a cell reference can be made to extract the day component.

Suppose you have dates in Excel as illustrated below:

Table

Since the function specifically speaks of serial_number as an argument, there could be a lot of time wasted in looking for the corresponding serial number for a date which we would love to avoid.

Instead, you can input the date directly inside the quotation marks such that the formula in C3 becomes =DAY("8/18/2022") to give you the result:

Result

However, hardcoding the dates only looks good as just a couple of date values are involved. What if you had thousands of rows of data and needed to extract the day for each of them?

In this case, we will directly reference the cell containing the date such that the formula in cell C3 becomes =DAY(B3) and drag it down to give the result:

Stats

We see that the day component from each of the dates represented in mm-dd-yyyy format is returned as a result in column C.

Examples of DAY Function

This section will show how you can use the function in real-life scenarios to take your financial and data analysis skills to the next level.

a. Analyzing transactions for each day

Suppose you decide to analyze the daily ledger for your firm. First, you extract all the relevant data of the daily transactions, as illustrated below:

Transactions

In this case, we need some mechanism to group our data per daily transaction. This can be achieved with the help of the DAY function along with the pivot tables.

First, we will use the formula =DAY(B3) in cell F3 and drag it down till cell F20, which gives the result as

Results

Now that we have the building blocks for creating groups using the date, we will create a pivot table showing a detailed summary of different line items in the ledger.

We will select the entire table and click on Insert > Pivot Tables > From Table/ Range, which opens the dialog box as illustrated below:

Dialog Box

We can either opt for a new worksheet to create the pivot table or use the existing one. In this example, we will use the current worksheet, the ‘Sheet1,’ select cell reference as location and click on Ok.

We will assign headers ‘Day’ and ‘Particulars’ in the Rows areas. At the same time, ‘Cr’ and ‘Dr’ will be transferred to the Values area, as illustrated below:

Pivot Table

Once the pivot table is created, right-click on it and click on PivotTable Options. Next, enable ‘Merge and center cells with labels’ from the ‘Layout and Format’ tab and ‘Classic PivotTable Layout’ from the ‘Display’ tab.

Set the Subtotal filters to none for all the subtotals you see in the pivot table. The final table you will see is illustrated below:

Final Table

This gives you a detailed insight into how many purchases and sales were made each day, along with the returns made for the goods sold for a particular day.

You can break down the particular into smaller objectives for a more in-depth analysis using the combination of the DAY function and pivot tables.

b. Condition-based results

Suppose Amazon Inc. offers product delivery through Prime services or the standard delivery procedures. The product is usually delivered within a day if a customer orders via Prime services.

Let’s say the data looks as illustrated below:

Customer Data

We want to send a customized text message to the customers on their cell phones regarding the expected delivery date.

Here, we will use the formula ="Product will be delivered on " &DAY(IF(D3="Yes", B3+1,B3+2))&IF(B3<EOMONTH(B3,0)," of this month,"" of next month"), which gives the result as

Delivery Dates

We have concatenated text strings and added IF functions in the formula, which evaluates the given conditions and returns a complete text string.

  • Apart from the concatenated text strings at either end, the IF statements first evaluate whether the customer is a prime customer based on the inputs in column D.
  • If the customer is an Amazon Prime service user, delivery will be in T + 1 day or T + 2 days.
  • The day component is extracted from the resultant date.
  • Another date and time function, EOMONTH, is used to return the end of the month date.
  • If the given date is less than EOMONTH, another condition is input that completes the text string to return the final output.

This is a simplified way of sending mass emails or text messages based on conditions.

It is possible to connect the Excel document with Outlook or other software that can create a query based on Order Id and send out the Delivery date information to the respective customers easily.

DAY vs. DAYS Function

When you type in the function name in the Excel cell following the equal sign, you will see another function pop up with a similar name called DAYS.

DAYS is a date and time function that returns the number of days between two given dates.

For example, if you have the dates 14th August 2022 and 19th August 2022, the function returns 5, which is the number of days that separates both dates.

The syntax for the function is:

=DAYS(end_date, start_date)

where,

  • end_date - (required) the ending date between which we want to know the number of days
  • start_date - (required) the starting date between which we want to know the number of days

Let’s see an example of what happens when you use the DAYS function. Suppose you have the data as illustrated below:

Chart

To get the difference between both days in terms of days, we will use the formula =DAYS(B3,C3) in cell D3 and drag it down till cell D7, which gives the result:

Table

You see, instead of extracting the day component, the function calculates the difference between both dates in terms of days.

Even though the result would be quite obvious, we would still advise you to carefully select the right function before you type in either function in the selected cell, as both return differing values.

MONTH vs. DAY Function

MONTH is categorized as a date and time function that returns a number between 1 to 12, corresponding to the twelve months from January to December in a calendar year.

For example, if the date is 14th July 2022, then using the MONTH function, you will get the result as 7, indicating the seventh month in a calendar year.

The syntax for the MONTH function is

=MONTH(serial_number)

where

  • serial_number - (required) a serial number corresponding to a date, a cell containing a date, or a formula that returns the date, which can be used to extract the month.

Suppose you have the data as illustrated below:

Table

To get the month component, we will use the formula =MONTH(B3) in cell C3 and drag it down to cell C7, which gives the result:

Excel Sheet

In contrast to the months, we will extract the day using the formula =DAY(B3) in cell D3 and drag it down till cell D7, which gives the day components as

Tabular Chart

Both functions can be used in combination to input date-based conditions and return dates that adhere to those conditions.

YEAR vs. DAY Function

The YEAR is another date and time function that extracts the year component from a given date.

We know that dates in Excel are stored as serial numbers from 1st January 1900 to 31st December 9999. Thus, the YEAR function can only return all the years between 1900 .x 9999.

For example, if you have the date as 14th Feb 1988 and use the YEAR function, the result will equal 1988.

The syntax for the function is

=YEAR(serial_number)

where

  • serial_number - (required) a serial number corresponding to a date, a cell containing a date, or a formula that returns the date, which can be used to extract the year.

Suppose we have the below data:

Data

To get the year component, we will use the formula =YEAR(B3) in cell C3 and drag it down to cell C7, which gives the result:

Table

We see that in most cells, the year is extracted in column C except in cell C7, where we have a #VALUE! Error. As we said earlier, all the dates before the 1st of January 1900 are not stored as serial numbers in Excel; hence, the function cannot identify the date.

On the other hand, using the =DAY(B3) in cell D3 and dragging it to cell D7, we get:

Values

Again, since Excel cannot identify the stored date, the function cannot extract the day even though it is an acceptable value. Thus, the dates not stored in Excel as serial numbers do not return any of the components.

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul GuptaLinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: