Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
MissNadine
 
Posts: n/a
Default Please help!! Vacation Accrual Formula


OK, I actually created this formula several years ago at my old job but
since I am no longer there, I have no access to it. My boss helped me
with it and I just can't remember the formula.

Basically, I need to create a spreadsheet showing number of accumulated
vacation days for an employee based on the number of days they accrue
per day per year.
Here is the pertinent information - all I remember from the last time I
had to deal with this formula.
We created a formula that basically used the current date (of the day
you actually accessed the spreadsheet) to calculate the number of days
(with like 2 decimal places) the employee had accrued to date.

This is the info we used to generate the number:

Anniversary date
Days accrued for year 1, 2, 3, 4, 5+
Year one - 5 days
Year two - 5 days
Year three - 10 days
Year four - 10 days
Year five and each year thereafter - 15 days

Accrual rate for each year:

Year one: .01369 days per day
Year two: .01369 days per day
Year three: .02739 days per day
Year four: .02739 days per day
Year five and on: .04109 days per day

Also taking in consideration that any days not used the previous year,
roll over to the next year.

For example, employee one has an anniversary date of 7/1/2003. As of
7/1/2005 there are no days accrued because they all have been used so
I'm starting from zero. To date, there have been 48 days since her
anniversary date, therefore, she has accrued 1.31 days to date. It
takes approximately 36.5 days to accrue 1 vacation day.

can someone please help me with this?

Thanks
Nadine


--
MissNadine
------------------------------------------------------------------------
MissNadine's Profile: http://www.excelforum.com/member.php...o&userid=26394
View this thread: http://www.excelforum.com/showthread...hreadid=396920

  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
wrote:


OK, I actually created this formula several years ago at my old job but
since I am no longer there, I have no access to it. My boss helped me
with it and I just can't remember the formula.

Basically, I need to create a spreadsheet showing number of accumulated
vacation days for an employee based on the number of days they accrue
per day per year.
Here is the pertinent information - all I remember from the last time I
had to deal with this formula.
We created a formula that basically used the current date (of the day
you actually accessed the spreadsheet) to calculate the number of days
(with like 2 decimal places) the employee had accrued to date.

This is the info we used to generate the number:

Anniversary date
Days accrued for year 1, 2, 3, 4, 5+
Year one - 5 days
Year two - 5 days
Year three - 10 days
Year four - 10 days
Year five and each year thereafter - 15 days

Accrual rate for each year:

Year one: .01369 days per day
Year two: .01369 days per day
Year three: .02739 days per day
Year four: .02739 days per day
Year five and on: .04109 days per day

Also taking in consideration that any days not used the previous year,
roll over to the next year.

For example, employee one has an anniversary date of 7/1/2003. As of
7/1/2005 there are no days accrued because they all have been used so
I'm starting from zero. To date, there have been 48 days since her
anniversary date, therefore, she has accrued 1.31 days to date. It
takes approximately 36.5 days to accrue 1 vacation day.

can someone please help me with this?

Thanks
Nadine


Here's one way that might work, but you'll need to check it.

I did note in your description that you seem to assume 365 day years (making no
special provision for leap year).

Set up a table like so:


Yrs vac'tn vac'tn
wrk accrued earned
0 0 5
1 5 5
2 10 10
3 20 10
4 30 15
5 45 15
6 60 15
7 75 15
8 90 15
9 105 15
10 120 15

Extend it down for however many years of work might be a maximum

Column 3 is the number of days earned in each year after completing the year in
column 1.

Column 2 has the formula =vac'tn accrued + vac'tn earned referenced to the
previous year (except for year 0, where a 0 is entered).

Then you can set up another table like so:

Employee DateOfHIre DaysEarned DaysUsed Balance
Smith 7/1/2003 11.315 10 1.315

The formula in DaysEarned is:

=VLOOKUP(DATEDIF(DateOfHIre,TODAY(),"y"),tbl,2)+
VLOOKUP(DATEDIF(DateOfHIre,TODAY(),"y"),tbl,3)/365
*DATEDIF(DateOfHIre,TODAY(),"yd")

Balance = DaysEarned-DaysUsed

The other entries are made manually.


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplify formula Luke Excel Worksheet Functions 37 May 6th 05 07:21 AM
how do i write a formula and keep in in formula form, so it DOESN. norcalchick2207 Excel Discussion (Misc queries) 2 February 4th 05 09:38 PM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM
Cell shows formula and not the result of the formula. stumpy1220 Excel Worksheet Functions 2 January 14th 05 06:11 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 11:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"