Years of Service Accrual

[Toby]

I have created a spreadsheet and I am now stumped on the last formula!

This is what I would like the formula to automatically calculate.

I would like the formula to recognize the employee's daily status'and
calculate based on a yearly percentage how much time was accrued.


If a Full Timer's Years Of Service accrual = 7.5 hours/year
If a Part Timer's Years Of Service accrual = 3.0 hours/year

e.g.

If I have worked as a full time employee for 5 months and then as a
part time 3 employee for 7 months

full time for 5 months= 3.125 hours
part time for 7 months= 1.75 hours
total accrual = 4.875 hours

Any ideas?
Thanks!
Toby

 

[dvt]

I have created a spreadsheet and I am now stumped on the last formula!

This is what I would like the formula to automatically calculate.

I would like the formula to recognize the employee's daily status'and
calculate based on a yearly percentage how much time was accrued.


If a Full Timer's Years Of Service accrual = 7.5 hours/year
If a Part Timer's Years Of Service accrual = 3.0 hours/year

e.g.

If I have worked as a full time employee for 5 months and then as a
part time 3 employee for 7 months

full time for 5 months= 3.125 hours
part time for 7 months= 1.75 hours
total accrual = 4.875 hours

You need 4 numbers: begin_date_parttime, end_date_parttime,
begin_date_fulltime, end_date_fulltime. I think these are self-explanatory.
Your formula would then be something like:

=7.5*YEARFRAC(begin_date_fulltime, end_date_fulltime) +
3*YEARFRAC(begin_date_parttime, end_date_parttime)

That should all be one formula -- eliminate the extra break caused by word
wrap. The YEARFRAC function is part of the Analysis ToolPak, so you will
need to install that if you don't have it. Finally, the DATEDIF function
might be useful instead of YEARFRAC but it doesn't seem to appear in Excel's
help.

Dave
dvt at psu dot edu