🚀 Data Analyst Roadmap — Part 7
📅 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:
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
📅 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