๐ Excel โ Level 6: Date & Time Functions for Data Analysis
Dates are everywhere in data analytics.
Think about datasets containing: Order dates, Transaction dates, Employee joining dates, Invoice dates, Payment dates, Due dates, Delivery dates, Project start/end dates, Customer registration dates
A Data Analyst often needs to answer questions such as:
How many orders were placed in January?
How long did customers wait for delivery?
Which month had the highest sales?
How many days overdue are invoices?
How many years has an employee worked?
To answer these questions, you need to understand Excel's date and time functions.
1๏ธโฃ How Excel Stores Dates
One important concept is that Excel stores dates as numbers internally.
For example, a date such as: 01-Jan-2026 is represented internally by a serial number.
This is why Excel can perform calculations such as: =B2-A2
If: A2 = 01-Jan-2026, B2 = 10-Jan-2026 the result can be: 9 meaning 9 days between the dates.
This is the foundation of date calculations in Excel.
2๏ธโฃ TODAY()
TODAY() returns the current date. =TODAY()
For example, if today's date is August 25, 2026, Excel returns: 25-Aug-2026
The value automatically changes when the date changes.
Common uses: Employee tenure, Age calculations, Overdue invoices, Days remaining, Current reporting period, Aging analysis
3๏ธโฃ NOW()
NOW() returns the current date and time. =NOW()
Example: 25-Aug-2026 01:38
The exact result depends on when Excel recalculates.
TODAY vs NOW:
TODAY() โ Current date, NOW() โ Current date + current time
4๏ธโฃ DATE()
DATE() creates a valid Excel date from year, month and day. =DATE(2026,8,25) Result: 25-Aug-2026
This is useful when dates need to be constructed from separate columns.
For example: Year: 2026, Month: 8, Day: 25 - You can create the date with: =DATE(A2,B2,C2)
5๏ธโฃ YEAR()
YEAR() extracts the year from a date. Suppose: A2 = 25-Aug-2026 Use: =YEAR(A2) Result: 2026
Common uses: Yearly reporting, Year-over-year analysis, Creating Year columns, Grouping transactions by year
6๏ธโฃ MONTH()
MONTH() extracts the month number. =MONTH(A2)
For: 25-Aug-2026 the result is: 8 because August is the eighth month.
7๏ธโฃ DAY()
DAY() extracts the day of the month. =DAY(A2)
For: 25-Aug-2026 result: 25
8๏ธโฃ Create Year, Month and Day Columns
Suppose you have: Order Date - 15-Jan-2026, 20-Feb-2026, 10-Mar-2026
You can create: Year: =YEAR(A2), Month Number: =MONTH(A2), Day: =DAY(A2)
This can help you analyze data by different time periods.
9๏ธโฃ EOMONTH()
EOMONTH() returns the last day of a month. Syntax: =EOMONTH(start_date,months)
Suppose: A2 = 15-Aug-2026
Use: =EOMONTH(A2,0) Result: 31-Aug-2026
Next month's end: =EOMONTH(A2,1) Result: 30-Sep-2026
Previous month's end: =EOMONTH(A2,-1) Result: 31-Jul-2026
๐ Why EOMONTH() Is Useful
It's extremely useful for: Month-end reporting, Financial reporting, Invoice analysis, Aging reports, Monthly dashboards, Closing processes
For example: "Give me all transactions up to the end of the reporting month." EOMONTH() becomes very useful here.
1๏ธโฃ1๏ธโฃ EDATE()
EDATE() moves a date forward or backward by a specified number of months.
Suppose: A2 = 25-Aug-2026