Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accrual Formula
I've got a beauty for you.
From 0-4 years, the employees get 0.83 days accrued per month. From 5-8 years, the employess get 1.25 days accrued per month. 9 and over years, they get 1.70 days per month. I've currently got the worksheet to calculate starting from Jan 1st of each year through December 31st, depending on the years they worked. What I don't have is if they start in the middle of the year, how to calculate accrued time. Also, I've got it set up so that at todays date, if I've used more vacation time than I've accrued, this is the amount that I have to pay back. However, we also need to have it show 30 or 60 days from now in case the employee gives notice. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accrual Formula
It's accrual world.
But seriously, don't start on Jan 1. Start on the hire date instead. -- Gary's Student gsnu200703 "Corey H." wrote: I've got a beauty for you. From 0-4 years, the employees get 0.83 days accrued per month. From 5-8 years, the employess get 1.25 days accrued per month. 9 and over years, they get 1.70 days per month. I've currently got the worksheet to calculate starting from Jan 1st of each year through December 31st, depending on the years they worked. What I don't have is if they start in the middle of the year, how to calculate accrued time. Also, I've got it set up so that at todays date, if I've used more vacation time than I've accrued, this is the amount that I have to pay back. However, we also need to have it show 30 or 60 days from now in case the employee gives notice. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accrual Formula
Here is a partial model:
in A1 enter: 1/1/2007 In B1 enter a typical hire date 2/15/2002 In C1 enter: =DATEDIF(B1,A1,"y") In D1 enter: =(C1<5)*0.83+(C1=5)*(C1<9)*1.25+(C1=9)*1.7 In A2 enter: =DATE(YEAR(A1),MONTH(A1)+1,1) copy A2 down thru A12 copy B1 down thru B12 copy C1 down thru C12 copy D1 down thru D12 -- Gary''s Student gsnu200703 "Corey H." wrote: I've got a beauty for you. From 0-4 years, the employees get 0.83 days accrued per month. From 5-8 years, the employess get 1.25 days accrued per month. 9 and over years, they get 1.70 days per month. I've currently got the worksheet to calculate starting from Jan 1st of each year through December 31st, depending on the years they worked. What I don't have is if they start in the middle of the year, how to calculate accrued time. Also, I've got it set up so that at todays date, if I've used more vacation time than I've accrued, this is the amount that I have to pay back. However, we also need to have it show 30 or 60 days from now in case the employee gives notice. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Accrual Formula
I assume that the vacation year runs from January 1 through to December
31st. How do you work out the number of years? Is it full years completed up to the previous January or years from the start date of the person. If from the start date then - say - if someone starts on July 1 and has completed 5 years service, in September do you calculate 6 months at 0.83 (because up until the start date he/she did not have 5 full years service), and 2 months at 1.25 days? Are accrued days rounded up or down to the nearst whole number of days? Do you want to forcast for 30 and 60 days or one and two months? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Corey H." wrote in message ... I've got a beauty for you. From 0-4 years, the employees get 0.83 days accrued per month. From 5-8 years, the employess get 1.25 days accrued per month. 9 and over years, they get 1.70 days per month. I've currently got the worksheet to calculate starting from Jan 1st of each year through December 31st, depending on the years they worked. What I don't have is if they start in the middle of the year, how to calculate accrued time. Also, I've got it set up so that at todays date, if I've used more vacation time than I've accrued, this is the amount that I have to pay back. However, we also need to have it show 30 or 60 days from now in case the employee gives notice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Vacation Accrual formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |