Calculate Accrued Leave Time

  • Thread starter Thread starter MargaretA
  • Start date Start date
M

MargaretA

I need to be able to track accrued leave time, but have no idea how to set up
the formula. Here are the particulars:

Accrual rate = 4.62 hrs. of leave per month, # of months = 12

I occasionally use a few hours in a month.

This year, I began employment in mid-April, so would you be so kind as to
provide 2 different formulas? (1 for a regular year, Jan.- Dec., and another
for this year mid-April - Dec.).

Thanks, in advance, for help!

Margaret
 
It's accurial world!

In A1 enter 4/15/2008
In B1 enter 0

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) and copy down

In B2 enter:
=B1+4.62 and copy down.

You will see:

4/15/2008 0
5/15/2008 4.62
6/15/2008 9.24
7/15/2008 13.86
8/15/2008 18.48
9/15/2008 23.1
10/15/2008 27.72
11/15/2008 32.34
12/15/2008 36.96
 
MargaretA,
You will need to make sure that the Analylis ToolPak is installed in Excel.
The following will give you the results to the last full month a person
accrues. Make sure Columns "D" & "E" are formatted as "General".
This will work if a person was working on, or after the first of the year.

Header:
A1: Employee
B1: Start Date
C1: End Date
D1: Accured Months
E1: Accured Leave

Entries:
A2: J. Smith
B2: 4/15/08
C2: =today()
D2: =DATEDIF($B2,$C2,"m")
E2: =4.62*$D2&" Hrs"
Drag the formulas in D2 & E2 down their respective Columns

Which would look something like this:
A B C D
E
1. Employee Start_Date End_Date Acc_Month Acc_Leave
2. J. Smith 4/15/08 10/31/08 6 27.72 Hrs
3. J. Doe 1/1/08 10/31/08 9 41.58
Hrs


Or even simplier put:
A B C D
1. Employee Start_Date End_Date Acc_Leave
2. J. Smith 4/15/08 =today() =4.62*DATEDIF($B2,$C2,"m")&"
Hrs"
3. J. Doe 1/01/08 =today()
=4.62*DATEDIF($B3,$C3,"m")&" Hrs"

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


I need to be able to track accrued leave time, but have no idea how to set
up
the formula. Here are the particulars:

Accrual rate = 4.62 hrs. of leave per month, # of months = 12

I occasionally use a few hours in a month.

This year, I began employment in mid-April, so would you be so kind as to
provide 2 different formulas? (1 for a regular year, Jan.- Dec., and
another
for this year mid-April - Dec.).

Thanks, in advance, for help!

Margaret
 
Back
Top