Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculate Accrued Leave Time
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 -- MargaretA |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculate Accrued Leave Time
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 -- Gary''s Student - gsnu200810 "MargaretA" wrote: 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 -- MargaretA |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Calculate Accrued Leave Time
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 "MargaretA" wrote in message ... 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 -- MargaretA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to: If D4 = Yes then Calculate C4/11, if D4 = no leave blank | Excel Worksheet Functions | |||
Tracking accrued sick time with excel | Excel Discussion (Misc queries) | |||
And another thing - time accrued calculation | Excel Discussion (Misc queries) | |||
Time To Leave | Excel Discussion (Misc queries) |