For example: "25/08/2026" may be stored as text.
Then functions such as: =YEAR(A2) may not work as expected.
You need to ensure the value is converted into a genuine Excel date before performing calculations.
This is a crucial data-cleaning concept.
🧪 Practical Interview Challenge
Suppose you have:
Employee: John, Joining Date: 15-Jan-2022, End Date: 25-Aug-2026
Sarah, 20-Mar-2021, 25-Aug-2026
Mike, 10-Jul-2023, 25-Aug-2026
Q1. Extract the joining year: =YEAR(B2)
Q2. Extract the joining month: =MONTH(B2)
Q3. Calculate completed years: =DATEDIF(B2,C2,"Y")
Q4. Calculate total days: =C2-B2
Q5. Find month-end for joining month: =EOMONTH(B2,0)
Q6. Find six months after joining: =EDATE(B2,6)
Q7. Calculate working days: =NETWORKDAYS(B2,C2)
🏆 Key Lesson
Dates aren't just values displayed on a spreadsheet. They allow you to analyze time.
A Data Analyst should be able to answer:
When did it happen? How long did it take? How many working days did it take? Which month did it happen in? Which quarter/year did it happen in? Is it overdue? When will it be due?
Once you become comfortable with date functions, you'll be able to build much more useful analysis around trends, aging, SLAs, employee tenure, financial periods and time-based KPIs.
Double Tap ❤️ For Part-8
Then functions such as: =YEAR(A2) may not work as expected.
You need to ensure the value is converted into a genuine Excel date before performing calculations.
This is a crucial data-cleaning concept.
🧪 Practical Interview Challenge
Suppose you have:
Employee: John, Joining Date: 15-Jan-2022, End Date: 25-Aug-2026
Sarah, 20-Mar-2021, 25-Aug-2026
Mike, 10-Jul-2023, 25-Aug-2026
Q1. Extract the joining year: =YEAR(B2)
Q2. Extract the joining month: =MONTH(B2)
Q3. Calculate completed years: =DATEDIF(B2,C2,"Y")
Q4. Calculate total days: =C2-B2
Q5. Find month-end for joining month: =EOMONTH(B2,0)
Q6. Find six months after joining: =EDATE(B2,6)
Q7. Calculate working days: =NETWORKDAYS(B2,C2)
🏆 Key Lesson
Dates aren't just values displayed on a spreadsheet. They allow you to analyze time.
A Data Analyst should be able to answer:
When did it happen? How long did it take? How many working days did it take? Which month did it happen in? Which quarter/year did it happen in? Is it overdue? When will it be due?
Once you become comfortable with date functions, you'll be able to build much more useful analysis around trends, aging, SLAs, employee tenure, financial periods and time-based KPIs.
Double Tap ❤️ For Part-8