+ Reply to Thread
Results 1 to 21 of 21

Please help!! Vacation Accrual Formula

  1. #1
    Registered User
    Join Date
    08-17-2005
    Posts
    2

    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

  2. #2
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  3. #3
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  4. #4
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  5. #5
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  6. #6
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  7. #7
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  8. #8
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  9. #9
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  10. #10
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  11. #11
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  12. #12
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  13. #13
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  14. #14
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  15. #15
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  16. #16
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  17. #17
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  18. #18
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  19. #19
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  20. #20
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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

  21. #21
    Ron Rosenfeld
    Guest

    Re: Please help!! Vacation Accrual Formula

    On Thu, 18 Aug 2005 12:27:40 -0500, MissNadine
    <MissNadine.1tz1yq_1124388391.6324@excelforum-nospam.com> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1