Working with DATE, DAY, MONTH, YEAR, and TODAY in Excel

Course: Advanced Excel

Topic: Working with DATE, DAY, MONTH, YEAR, and TODAY

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction

Microsoft Excel provides several date and time functions that help users create dates, extract individual date components, and perform date-based calculations. The DATE, DAY, MONTH, YEAR, and TODAY functions are particularly useful for organizing schedules, preparing financial reports, calculating employee service periods, tracking deadlines, and analyzing time-based business information.

Excel stores valid dates as serial numbers and displays them according to the selected cell format. Date functions make it possible to work with these values systematically instead of manually entering or extracting dates.

For example, the DATE function can create a date from separate year, month, and day values. DAY extracts the day number, MONTH extracts the month number, YEAR returns the year, and TODAY returns the current date according to the computer's date settings.

2. Learning Objectives

• Understand how Excel stores and displays dates.
• Learn the syntax and arguments of DATE, DAY, MONTH, YEAR, and TODAY.
• Create dates using separate year, month, and day values.
• Extract the day, month, and year from an existing date.
• Use TODAY to calculate date differences and track deadlines.
• Apply date functions to employee records, sales reports, invoices, and project schedules.
• Understand date formatting, common errors, and troubleshooting techniques.

3. Understanding Dates in Excel

Excel generally stores dates as serial numbers, with the displayed date depending on the cell's number format and the workbook's date system. This allows Excel to calculate date differences and perform arithmetic using dates.

For example, a valid date such as 15 March 2026 can be displayed as 15-Mar-2026, 15/03/2026, or March 15, 2026, depending on the formatting settings.

Changing a date's display format does not normally change its underlying date value. However, dates imported as text may need conversion before date calculations work correctly.

4. Understanding the DATE Function

4.1 Definition

The DATE function creates a valid Excel date from separate year, month, and day numbers. It is especially useful when date components are stored in different cells or generated by other formulas.

4.2 Syntax

=DATE(year, month, day)

4.3 Explanation of Arguments

4.4 Example: Creating a Date

To create 27 October 2026, enter:

=DATE(2026,10,27)

Result: A valid Excel date representing 27 October 2026. Format the result cell as dd-mmm-yyyy to display it as 27-Oct-2026.

4.5 Example: Creating a Date from Cell References

Suppose the worksheet contains the following information:

Formula:

=DATE(A2,B2,C2)

Result: 27-Oct-2026 when displayed using the dd-mmm-yyyy format.

4.6 Example: Creating the First Day of a Month

Suppose A2 contains a year and B2 contains a month number. To generate the first day of that month, use:

=DATE(A2,B2,1)

This is useful for monthly reporting, budgeting, and generating calendar periods.

4.7 Example: Finding the Last Day of a Month

The DATE function automatically normalizes overflowing day values. For example, day zero of a month represents the last day of the previous month.

=DATE(2026,11,0)

Result: 31-Oct-2026.

A common reusable formula for finding the last day of the month containing the date in A2 is:

=DATE(YEAR(A2),MONTH(A2)+1,0)

This formula works across different month lengths, including leap years.

4.8 Understanding DATE Normalization

DATE adjusts month and day values that exceed the usual ranges rather than rejecting every such input. For example:

=DATE(2026,13,1)

Result: 1-Jan-2027.

Similarly, =DATE(2026,2,30) normalizes to a date in March. This behavior is useful for date arithmetic, but it also means that invalid source components can produce an unexpected date without an error.

5. Understanding the DAY Function

5.1 Definition

The DAY function extracts the day of the month from a valid Excel date. Its result is a number from 1 to 31.

5.2 Syntax

=DAY(serial_number)

serial_number: A valid Excel date or a cell reference containing a valid date.

5.3 Example: Extracting the Day

Suppose A2 contains 27-Oct-2026.

=DAY(A2)

Result: 27

5.4 Example: Extracting the Day from a Fixed Date

=DAY(DATE(2026,10,27))

Result: 27

5.5 Practical Applications of DAY

• Identifying the day of the month for invoice records.
• Grouping transactions by calendar day.
• Checking whether a date falls on a particular day number.
• Preparing daily sales reports.
• Extracting date components for validation and analysis.

Remember that DAY returns the day of the month, not the weekday name. To identify the weekday, use functions such as WEEKDAY or TEXT with an appropriate format.

6. Understanding the MONTH Function

6.1 Definition

The MONTH function extracts the month number from a valid Excel date. It returns a number from 1 to 12, where 1 represents January and 12 represents December.

6.2 Syntax

=MONTH(serial_number)

6.3 Example: Extracting the Month Number

Suppose A2 contains 27-Oct-2026.

=MONTH(A2)

Result: 10

6.4 Example: Extracting a Month from a Date

=MONTH(DATE(2026,3,15))

Result: 3

6.5 Displaying the Month Name

MONTH returns a numeric month value. To display the full month name, use TEXT:

=TEXT(A2,"mmmm")

Example result: October, if A2 contains 27-Oct-2026.

To display an abbreviated month name, use:

=TEXT(A2,"mmm")

Example result: Oct.

6.6 Practical Applications of MONTH

• Grouping sales by month.
• Preparing monthly financial reports.
• Identifying transactions from a selected month.
• Building monthly summaries and dashboards.
• Calculating monthly service or billing periods.

7. Understanding the YEAR Function

7.1 Definition

The YEAR function extracts the year component from a valid Excel date. It returns the year as a number.

7.2 Syntax

=YEAR(serial_number)

7.3 Example: Extracting the Year

Suppose A2 contains 27-Oct-2026.

=YEAR(A2)

Result: 2026

7.4 Example: Extracting the Year from a Fixed Date

=YEAR(DATE(2024,2,29))

Result: 2024

This example uses a valid leap-year date.

7.5 Practical Applications of YEAR

• Grouping transactions by financial or calendar year.
• Analyzing annual sales performance.
• Calculating employee joining years.
• Organizing historical records.
• Preparing yearly comparison reports.

8. Understanding the TODAY Function

8.1 Definition

The TODAY function returns the current date based on the date maintained by the computer or calculation environment. It does not include the current time.

8.2 Syntax

=TODAY()

TODAY does not require any arguments. Enter the formula with empty parentheses.

8.3 Example: Displaying Today's Date

=TODAY()

Result: The current date when Excel recalculates the formula. The displayed date changes as the date changes and the workbook recalculates.

8.4 Example: Calculating Days Since a Date

Suppose A2 contains an employee's joining date. To calculate the number of days since that date, use:

=TODAY()-A2

Result: The number of days between the joining date and today's date, assuming A2 contains a valid date.

If the joining date is in the future, the result will be negative. You can use validation or an IF formula if future joining dates require special treatment.

8.5 Example: Checking Whether a Deadline Has Passed

Suppose B2 contains a task deadline. Use:

=IF(B2<TODAY(),"Overdue",IF(B2=TODAY(),"Due Today","Upcoming"))

The formula returns:

• Overdue: The deadline is before today.
• Due Today: The deadline is today.
• Upcoming: The deadline is after today.

For this example, B2 should contain a valid date without a time component. If deadlines contain timestamps, use an appropriate date-only comparison.

8.6 Example: Calculating Days Until a Deadline

Suppose B2 contains a future deadline.

=B2-TODAY()

Result: The number of calendar days remaining until the deadline. A negative value indicates that the date has passed.

8.7 TODAY Versus NOW

Both functions are volatile, meaning they can recalculate when Excel recalculates the workbook. They do not permanently record the date or time at which a record was created.

9. Comparison of DATE, DAY, MONTH, YEAR, and TODAY

10. Combining Date Functions

Date functions become especially useful when combined in a single formula. They can generate new dates, extract components, and build automatic reporting periods.

10.1 Extracting All Date Components

If A2 contains a valid Excel date, use the following formulas:

=DAY(A2)
=MONTH(A2)
=YEAR(A2)

These formulas return the day number, month number, and year, respectively.

10.2 Reconstructing a Date

You can extract date components and use DATE to reconstruct a date:

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

This recreates the date represented by A2. It is generally unnecessary for an already valid date, but it demonstrates how the functions work together.

10.3 Creating the First Day of the Current Month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

This formula returns the first day of the current month.

10.4 Creating the Last Day of the Current Month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

This formula returns the last day of the current month, including February's correct month-end in leap years.

10.5 Extracting the Current Year

=YEAR(TODAY())

This formula returns the current year and updates when the date changes and Excel recalculates.

10.6 Checking Whether a Date Falls in the Current Month

Suppose A2 contains a valid date. The following formula checks whether it belongs to the current month and year:

=AND(MONTH(A2)=MONTH(TODAY()),YEAR(A2)=YEAR(TODAY()))

Result: TRUE if A2 belongs to the current calendar month and year; otherwise, FALSE.

11. Practical Business Applications

11.1 Employee Joining Date Analysis

Human resources teams can extract the joining year and month from employee records.

=YEAR(B2)

If B2 contains the joining date, this formula returns the joining year. TODAY can also help calculate elapsed days since joining.

11.2 Invoice and Payment Tracking

Businesses can use TODAY to determine whether an invoice due date has passed.

=IF(C2<TODAY(),"Payment Overdue","Not Yet Overdue")

Here, C2 contains the invoice due date. This formula classifies dates before today as overdue; an invoice due today is not classified as overdue.

11.3 Monthly Sales Reporting

The MONTH function can extract the month number from each transaction date.

=MONTH(A2)

The YEAR function can extract the corresponding year:

=YEAR(A2)

These helper columns can be used to group transactions and build monthly or annual summaries.

11.4 Project Deadline Management

Suppose B2 contains a project deadline. Calculate the remaining days with:

=B2-TODAY()

This formula supports deadline monitoring and project dashboards. Consider adding conditional formatting to highlight negative values and approaching deadlines.

11.5 Generating Monthly Report Dates

To create the first day of a specified month, use DATE with the appropriate year and month:

=DATE(A2,B2,1)

This is useful when A2 contains a year and B2 contains a month number.

11.6 Calculating Calendar-Year Categories

To classify records by year, extract the year from the transaction date and use it in a PivotTable, filter, or summary formula.

=YEAR(A2)

This helps users compare performance across multiple calendar years.

12. Common Errors and Troubleshooting

13. Best Practices for Working with Excel Dates

• Use DATE to construct dates from separate numeric components.
• Ensure source values represent valid dates rather than unconverted text.
• Apply consistent date formats throughout a worksheet or report.
• Use DAY, MONTH, and YEAR when individual date components are required.
• Use TODAY for calculations that should respond to the current date.
• Remember that TODAY updates during recalculation and is not a permanent timestamp.
• Validate year, month, and day inputs before using DATE when the values come from external data.
• Use explicit formats such as dd-mmm-yyyy when sharing reports to reduce ambiguity.
• Check whether the business uses calendar years or a different financial-year convention.
• Test formulas around month-end, year-end, and leap-year dates.
• Keep original source dates when building helper columns for analysis.

14. Hands-On Practice Exercises

Exercise 1: Create a Date

Create a date representing 15 August 2026 using the DATE function.

Answer:

=DATE(2026,8,15)

Expected display: 15-Aug-2026 when formatted as dd-mmm-yyyy.

Exercise 2: Extract the Day

Cell A2 contains 15-Aug-2026. Extract the day of the month.

Answer:

=DAY(A2)

Expected result: 15

Exercise 3: Extract the Month

Cell A2 contains 15-Aug-2026. Extract the month number.

Answer:

=MONTH(A2)

Expected result: 8

Exercise 4: Extract the Year

Cell A2 contains 15-Aug-2026. Extract the year.

Answer:

=YEAR(A2)

Expected result: 2026

Exercise 5: Display the Current Date

Enter a formula that returns the current date.

Answer:

=TODAY()

Expected result: The current date when the formula recalculates.

Exercise 6: Calculate Days Remaining

Cell B2 contains a valid future deadline. Calculate the number of days remaining until that date.

Answer:

=B2-TODAY()

Exercise 7: Find the Last Day of the Current Month

Write a formula that returns the last day of the current month.

Answer:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

15. Mini Project: Employee Date Management Report

Objective: Build a simple Excel report that uses date functions to organize employee records and monitor upcoming dates.

Create a worksheet with these columns:

Task 1: Extract the joining day.

=DAY(B2)

Task 2: Extract the joining month.

=MONTH(B2)

Task 3: Extract the joining year.

=YEAR(B2)

Task 4: Calculate the days since joining.

=TODAY()-B2

Task 5: Create a reporting date for the first day of the current month.

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

Expected learning outcome: Learners will understand how to extract date components, construct reporting dates, and use the current date for dynamic calculations.

16. Interview Questions and Answers

Q1. What is the DATE function in Excel?

Answer: DATE creates a valid Excel date from year, month, and day components.

Q2. What does the DAY function return?

Answer: DAY returns the day of the month as a number from 1 to 31.

Q3. What does the MONTH function return?

Answer: MONTH returns the month number from 1 to 12.

Q4. What does the YEAR function return?

Answer: YEAR extracts the year from a valid Excel date.

Q5. What is the purpose of TODAY?

Answer: TODAY returns the current date without the time and updates when the formula recalculates.

Q6. How do you calculate the number of days between today and a deadline?

Answer: Subtract TODAY from the deadline date.

=A2-TODAY()

Q7. How can you extract the month name instead of its number?

Answer: Use TEXT with the "mmmm" format.

=TEXT(A2,"mmmm")

Q8. Can DATE accept month values greater than 12?

Answer: Yes. Excel normalizes overflowing month values into the appropriate year and month, which can be useful for date calculations.

Q9. What is the difference between TODAY and NOW?

Answer: TODAY returns the current date, whereas NOW returns the current date and time.

Q10. Why might Excel display a date as a number?

Answer: Excel stores dates as serial numbers. Applying a date number format displays the value as a readable date.

17. Quick Revision Notes

• DATE: Creates a date from year, month, and day values.
• DAY: Extracts the day of the month.
• MONTH: Extracts the month number.
• YEAR: Extracts the year.
• TODAY: Returns the current date.
• Days remaining: Subtract TODAY from a future deadline.
• Days elapsed: Subtract a past date from TODAY.
• Month name: Use =TEXT(A2,"mmmm").
• First day of current month: Use DATE with the current year and month and day 1.
• Last day of current month: Use DATE with the next month and day 0.
• Important: Validate source dates and apply suitable date formatting.

18. Conclusion

The DATE, DAY, MONTH, YEAR, and TODAY functions are essential tools for managing dates in Microsoft Excel. DATE constructs valid dates, DAY extracts the day number, MONTH returns the month number, YEAR identifies the year, and TODAY provides a dynamic current date for calculations.

By combining these functions, users can automate monthly reporting, organize employee records, track invoices, monitor project deadlines, and prepare date-driven dashboards. Practicing these formulas with real datasets builds a strong foundation for advanced Excel analysis and reporting.

Learn Advanced Excel with JustAcademy

Explore practical Excel training to improve your formula skills, data analysis, and reporting techniques.

Explore the Advanced Excel Course

Register for a Course Demo

Visit JustAcademy Official Website