Excel’s DAY, MONTH, and YEAR functions allow you to extract specific parts of a date. These functions are extremely useful when working with date-related data, whether it’s breaking down dates for analysis or preparing reports by day, month, or year. With just a simple formula, you can isolate the day, month, or year from a given date.
Syntax
Here’s the syntax for the DAY, MONTH, and YEAR functions in Excel:
| Function | Syntax |
|---|---|
| DAY | =DAY(date) |
| MONTH | =MONTH(date) |
| YEAR | =YEAR(date) |
date: A reference to the cell that contains the date you want to break down.
Example
Let’s say you are organizing events and want to extract the day, month, and year from the dates. The table below shows how to use the DAY, MONTH, and YEAR functions to isolate each component.
| Event | Date | Day | Month | Year |
|---|---|---|---|---|
| Conference | 12/09/2022 | =DAY(B2) | =MONTH(B2) | =YEAR(B2) |
| Seminar | 04/02/2023 | =DAY(B3) | =MONTH(B3) | =YEAR(B3) |
| Workshop | 28/11/2021 | =DAY(B4) | =MONTH(B4) | =YEAR(B4) |
In this example, the DAY, MONTH, and YEAR functions extract the relevant parts of each event date.
Practice Exercise 1
Task: You have a table of orders with their order dates. Your task is to extract the day, month, and year for each order and calculate how many days have passed since the order date.
Hint: Use the TODAY() function to calculate how many days have passed since the order date. Subtract the order date from today's date.
Practice Exercise 2
Task: You have several project start and end dates, and you need to extract the day, month, and year for both the start and end dates. Then, calculate the total number of days between the start and end dates using a simple subtraction.
Hint: Use simple date subtraction (End Date - Start Date) to find the total number of days between the start and end dates. The DAY, MONTH, and YEAR functions help extract individual components from the dates.
Solution to Exercise 1
Extracting date components and calculating days since the order date:
=DAY(B2)
=MONTH(B2)
=YEAR(B2)
=TODAY()-B2
=DAY(B3)
=MONTH(B3)
=YEAR(B3)
=TODAY()-B3
=DAY(B4)
=MONTH(B4)
=YEAR(B4)
=TODAY()-B4Solution to Exercise 2
Extracting date components and calculating total days between the start and end dates:
=DAY(B2)
=MONTH(B2)
=YEAR(B2)
=DAY(C2)
=MONTH(C2)
=YEAR(C2)
=C2-B2
=DAY(B3)
=MONTH(B3)
=YEAR(B3)
=DAY(C3)
=MONTH(C3)
=YEAR(C3)
=C3-B3
=DAY(B4)
=MONTH(B4)
=YEAR(B4)
=DAY(C4)
=MONTH(C4)
=YEAR(C4)
=C4-B4This simplified exercise uses basic date subtraction to calculate the number of days between the start and end dates, keeping it more approachable.