Age, Experience, Tenure and Date Difference in excel Practice Questions with Solutions

Introduction

Date calculations are common in employee records, student databases, customer information, subscriptions, and business reports. In this chapter, you will practice calculating age, years of experience, employee tenure, completed months, total days, and detailed date differences. The questions use practical Excel formulas such as DATEDIF, DAYS, YEARFRAC, and date arithmetic so you can test your Excel skills with different real-world examples. Age, Experience, Tenure and Date Difference in Excel Practice Questions with Solutions to help you understand the concepts.

Question 1: Calculate Age in Completed Years

Problem Statement

Calculate the current age of each person as of 28 September 2026.

Excel Data

Solution

In D2, enter:

=DATEDIF(B2,C2,"Y")

Copy the formula down.

Expected Output

DATEDIF with "Y" returns completed years.

Question 2: Calculate Age in Years and Months

Problem Statement

Calculate each person’s age in completed years and remaining months.

Excel Data

Solution

In D2, enter:

=DATEDIF(B2,C2,"Y")&" years "&DATEDIF(B2,C2,"YM")&" months"

Copy down.

Expected Output

"YM" returns the remaining complete months after completed years.

Question 3: Calculate Age in Years, Months and Days

Problem Statement

Calculate the complete age of each person in years, months, and days.

Excel Data

Solution

In D2, enter:

=DATEDIF(B2,C2,"Y")&" years "&DATEDIF(B2,C2,"YM")&" months "&DATEDIF(B2,C2,"MD")&" days"

Copy down.

Expected Output

Question 4: Calculate Employee Experience

Problem Statement

Calculate the completed years of professional experience for each employee as of 28 September 2026.

Excel Data

Solution

In D2, enter:

=DATEDIF(B2,C2,"Y")

Copy down.

Expected Output

Question 5: Calculate Employee Tenure in Years and Months

Problem Statement

Calculate how long each employee has worked for the company.

Display the result as:

8 years 3 months

Excel Data

Solution

In D2, enter:

=DATEDIF(B2,C2,"Y")&" years "&DATEDIF(B2,C2,"YM")&" months"

Copy down.

Expected Output

Question 6: Find the Total Number of Days Between Two Dates

Problem Statement

Calculate the total number of days taken to complete each project.

Excel Data

Solution

In D2, enter:

=DAYS(C2,B2)

Copy down.

Expected Output

Question 7: Calculate Complete Months of Membership

Problem Statement

A company wants to know how many complete months each customer has been a member.

Excel Data

Solution

In D2, enter:

=DATEDIF(B2,C2,"M")

Copy down.

Expected Output

Question 8: Calculate Experience as a Decimal Number of Years

Problem Statement

Calculate each employee’s experience as an approximate decimal number of years.

For example:

5.75 years

Excel Data

Solution

In D2, enter:

=YEARFRAC(B2,C2)

Copy down.

To display only two decimal places, use:

=ROUND(YEARFRAC(B2,C2),2)

Expected Output

YEARFRAC expresses the date difference as a fraction of a year.

Question 9: Calculate Remaining Days After Complete Years and Months

Problem Statement

Find the number of remaining days after calculating completed years and months.

Excel Data

Solution

In D2, enter:

=DATEDIF(B2,C2,"MD")

Copy down.

Expected Output

"MD" returns the remaining days after completed months are excluded.

Question 10: Build a Complete Employee Date-Difference Report

Problem Statement

Create an employee report that calculates:

1. Age in completed years
2. Company tenure in completed years
3. Tenure in years and months
4. Total number of days since joining

Use 28 September 2026 as the calculation date.

Excel Data

Solution

In E2, calculate age:

=DATEDIF(B2,D2,"Y")

In F2, calculate completed years of tenure:

=DATEDIF(C2,D2,"Y")

In G2, calculate years and months:

=DATEDIF(C2,D2,"Y")&" years "&DATEDIF(C2,D2,"YM")&" months"

In H2, calculate total days:

=DAYS(D2,C2)

Copy all formulas down.

Expected Output

This combines several date-difference techniques into one practical employee-report exercise.

Key Takeaways

• DATEDIF is useful for calculating completed years, months, and days between dates.
• DATEDIF(...,"Y") returns completed years.
• DATEDIF(...,"M") returns completed months.
• DATEDIF(...,"D") returns the total number of days.
• DATEDIF(...,"YM") returns remaining complete months after completed years.
• DATEDIF(...,"MD") returns remaining days after completed months.
• DAYS calculates the total number of days between two dates.
• YEARFRAC expresses a date difference as a fraction of a year.
• Age calculations can be created using DATEDIF.
• Employee experience and tenure can be calculated using the same technique.
• Combining DATEDIF with & lets you create readable results such as 8 years 3 months.
• Date calculations are useful for employee records, memberships, subscriptions, projects, student records, and customer databases.

FAQs

1. How can I calculate age in Excel?

If the date of birth is in A2 and the as-of date is in B2, use:

=DATEDIF(A2,B2,"Y")

This returns the completed age in years.

2. How can I calculate employee experience in Excel?

If the employee’s joining date is in A2 and the calculation date is in B2, use:

=DATEDIF(A2,B2,"Y")

This returns completed years of experience.

3. How can I calculate tenure in years and months?

Use:

=DATEDIF(A2,B2,"Y")&" years "&DATEDIF(A2,B2,"YM")&" months"

The result can look like:

6 years 7 months

4. What is the difference between DATEDIF and DAYS?

DATEDIF can calculate differences using different units such as years, months, and days.

DAYS specifically returns the number of days between two dates.

5. How can I calculate total days between two dates?

Use:

=DAYS(B2,A2)

Alternatively, Excel dates can also be subtracted:

=B2-A2

6. What does YEARFRAC do?

YEARFRAC calculates the fraction of a year represented by the number of days between two dates.

For example:

=YEARFRAC(A2,B2)

can return a value such as 6.60.

7. Can I calculate age in years, months, and days?

Yes. You can combine three DATEDIF calculations:

=DATEDIF(A2,B2,"Y")&" years "&DATEDIF(A2,B2,"YM")&" months "&DATEDIF(A2,B2,"MD")&" days"

8. What does the “YM” unit mean in DATEDIF?

"YM" returns the number of complete months remaining after completed years have been removed from the date difference.

9. What does the “MD” unit mean in DATEDIF?

"MD" returns the remaining number of days after completed months have been removed.

10. Where are age and tenure calculations used?

They are commonly used in employee databases, HR reports, student records, membership systems, customer databases, subscriptions, project tracking, and other date-based reports.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar