DAY Function in Microsoft Excel
Part 1: Introduction
Definition
The DAY function in Microsoft Excel is a date/time function that returns the day of a given date as an integer, ranging from 1 to 31.
Purpose
The purpose of the DAY function is to extract the day from a specified date.
Syntax & Arguments
The syntax for the DAY function is as follows:
DAY(serial_number) serial_number: This is a required argument. It represents the date of the day you are trying to find. Dates should be entered using the DATE function or as results of other formulas or functions. For example, useDATE(2008,5,23)for the 23rd day of May 2008. Problems can occur if dates are entered as text.
Return Value
The DAY function returns the day of a date represented by a serial number as an integer, ranging from 1 to 31.
Remarks
Microsoft Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900. The values returned by the YEAR, MONTH, and DAY functions will be Gregorian values regardless of the display format for the supplied date value.
Part 2: Examples
Let’s look at some examples of using the DAY function in business.
Example 1
Purpose: To find the day of the invoice date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Invoice Date | Day |
| 2 | 2023-05-15 | =DAY(A2) |
| 3 | 2023-06-20 | =DAY(A3) |
| 4 | 2023-07-25 | =DAY(A4) |
Explanation: In this example, we have invoice dates and want to find the day of these dates. The formula =DAY(A2) returns the day of the date in cell A2. The results would be 15, 20, and 25, respectively.
Example 2
Purpose: To categorize sales data by the day of the sale date.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Sale Date | Sales | Day |
| 2 | 2023-01-15 | 5000 | =DAY(A2) |
| 3 | 2023-02-20 | 6000 | =DAY(A3) |
| 4 | 2023-03-25 | 7000 | =DAY(A4) |
Explanation: In this example, we have sales data, and we want to categorize this data by the day of the sale date. The formula =DAY(A2) returns the day of the date in cell A2. The results would be 15, 20, and 25, respectively.
Example 3
Purpose: To track the day of the received payments.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Payment Date | Amount | Day |
| 2 | 2023-04-15 | 1500 | =DAY(A2) |
| 3 | 2023-05-20 | 2000 | =DAY(A3) |
| 4 | 2023-06-25 | 2500 | =DAY(A4) |
Explanation: In this example, we have payment dates and want to track the day of these payments. The formula =DAY(A2) returns the day of the date in cell A2. The results would be 15, 20, and 25, respectively.
Example 4
Purpose: To determine the day of the contract start date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Contract Start | Day |
| 2 | 2023-07-01 | =DAY(A2) |
| 3 | 2023-08-15 | =DAY(A3) |
| 4 | 2023-09-30 | =DAY(A4) |
Explanation: In this example, we have contract start dates and want to determine the day of these dates. The formula =DAY(A2) returns the day of the date in cell A2. The results would be 1, 15, and 30, respectively.
Example 5
Purpose: To find the day of the delivery date.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Delivery Date | Day |
| 2 | 2023-10-15 | =DAY(A2) |
| 3 | 2023-11-20 | =DAY(A3) |
| 4 | 2023-12-25 | =DAY(A4) |
Explanation: In this example, we have delivery dates and want to find the day of these dates. The formula =DAY(A2) returns the day of the date in cell A2. The results would be 15, 20, and 25, respectively.
Example 6: Using DAY with IF Function
Purpose: To classify sales into early, mid, and late months based on the day of the sale date.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Sale Date | Sales | Period |
| 2 | 2023-01-10 | 5000 | =IF(DAY(A2)<=10, “Early”, IF(DAY(A2)<=20, “Mid”, “Late”)) |
| 3 | 2023-01-15 | 6000 | =IF(DAY(A3)<=10, “Early”, IF(DAY(A3)<=20, “Mid”, “Late”)) |
| 4 | 2023-01-25 | 7000 | =IF(DAY(A4)<=10, “Early”, IF(DAY(A4)<=20, “Mid”, “Late”)) |
Explanation: In this example, we have sales data, and we want to classify this data into early, mid, and late months based on the day of the sale date. The nested IF function with the DAY function helps to classify the data.
Example 7: Using DAY with the SUM Function
Purpose: To calculate the total sales for a specific day of the month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-02-15 | 6000 |
| 4 | 2023-03-15 | 7000 |
| 5 | 2023-04-15 | 8000 |
| 6 | Total Sales for 15th | =SUM(IF(DAY(A2:A5)=15, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to calculate the total sales for the 15th day of any month. The SUM function combined with the DAY function helps to sum the sales for the 15th day.
Example 8: Using DAY with VLOOKUP Function
Purpose: To find the sales data for a specific day of the month from a lookup table.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Day | Sales |
| 2 | 10 | 5000 |
| 3 | 15 | 6000 |
| 4 | 20 | 7000 |
| 5 | 25 | 8000 |
| 6 | Sale Date | 2023-03-15 |
| 7 | Sales for Sale Date Day | =VLOOKUP(DAY(B6), A2:B5, 2, FALSE) |
Explanation: In this example, we have a lookup table with sales data for each day of the month. We want to find the sales for the day of a specific sale date. The VLOOKUP function combined with the DAY function helps to find the deals for the day of the sale date.
Example 9: Using DAY with AVERAGE Function
Purpose: To calculate the average sales for a specific day of the month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-02-15 | 6000 |
| 4 | 2023-03-15 | 7000 |
| 5 | 2023-04-15 | 8000 |
| 6 | Average Sales for 15th | =AVERAGE(IF(DAY(A2:A5)=15, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to calculate the average sales for the 15th day of any month. The AVERAGE function combined with the DAY function helps to estimate the average sales for the 15th day.
Example 10: Using DAY with COUNTIF Function
Purpose: To count the number of sales made on a specific day of the month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-02-15 | 6000 |
| 4 | 2023-03-15 | 7000 |
| 5 | 2023-04-15 | 8000 |
| 6 | Number of Sales on 15th | =COUNTIF(A2:A5, “=15”) |
Explanation: In this example, we have sales data and want to count the number of sales made on the 15th day of any month. The COUNTIF function combined with the DAY function helps to count the number of sales made on the 15th day.
Example 11: Using DAY with MAX Function
Purpose: To find the maximum sales made on a specific day of the month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-02-15 | 6000 |
| 4 | 2023-03-15 | 7000 |
| 5 | 2023-04-15 | 8000 |
| 6 | Maximum Sales for 15th | =MAX(IF(DAY(A2:A5)=15, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to find the maximum sales made on the 15th day of any month. The MAX function combined with the DAY function helps to find the top deals made on the 15th day.
Example 12: Using DAY with MIN Function
Purpose: To find the minimum sales made on a specific day of the month.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Sale Date | Sales |
| 2 | 2023-01-15 | 5000 |
| 3 | 2023-02-15 | 6000 |
| 4 | 2023-03-15 | 7000 |
| 5 | 2023-04-15 | 8000 |
| 6 | Minimum Sales for 15th | =MIN(IF(DAY(A2:A5)=15, B2:B5, 0)) |
Explanation: In this example, we have sales data and want to find the minimum sales made on the 15th day of any month. The MIN function combined with the DAY function helps to find the minimum deals made on the 15th day.
Part 3: Tips and Tricks
The DAY function can be combined to create more complex formulas. For example, you can use it with the IF function to perform actions based on the day of a date.
Remember that Excel stores date as sequential serial numbers so that you can use these numbers in calculations.
Be careful when entering dates as text. Using the DATE function or the results of other formulas or functions is recommended to avoid any issues.
The DAY function will return Gregorian values regardless of the display format for the supplied date value.