Business Tools Blog

Problem: I had to calculate how many PTO hours employees should be accruing.  If an employee’s  tenure was 0-35 months, the accrual was 15 days of PTO per year.  If an employee’s tenure was 36 to 59 months, the accrual was 20 days per year.  If an employee’s tenure was =>60 months, the accrual was 25 days per year.

Solution: Excel “DATEDIF” formula to the rescue.  This formula compares two dates and returns whole months, years, or days.

For an employee with a hire date of 6/12/2006, I calculated the number of months worked:

=DATEDIF(A2, NOW(),”M”)

• A2= 6/12/2006
• NOW() = today 7/29/08

the answer is 25 months

The same formula can be used to calculate Years, by using “Y”; or used to calculate days, by using “D”  (see rows 3 & 4 in the picture below)

This formula is also useful for contract administration.