I need to set up a worksheet that will allow me to calculate vacation days
from Hire date as follows: Employee works for 90 days (intro period), then
will accrue 1 vacation day at the beginning of the first of the following
month and one additional vacation day for each month after for a maximum of
10 days per year. Thereafter they receive 10 days on January 1st after their
first 12 months of service. 4th thru 9th they will recieve 15 days on
January 1st.
--
cao

On Wed, 4 Feb 2009 13:06:22 -0800, srctr
wrote:

I need to set up a worksheet that will allow me to calculate vacation days
from Hire date as follows: Employee works for 90 days (intro period), then
will accrue 1 vacation day at the beginning of the first of the following
month and one additional vacation day for each month after for a maximum of
10 days per year.

Thereafter they receive 10 days on January 1st after their
first 12 months of service. 4th thru 9th they will recieve 15 days on
January 1st.

Does this mean that they receive 10 days on Jan 1 for years 1,2 and 3; and then
15 days on Jan 1 after that?

What happens during the 10th year?

Hopefully there is a simpler formula, but this seems to work for the parameters
you've mentioned.

The two relevant dates are the "HireDate" and the "AsOfDate" The latter is
the date being evaluated for how much vacation has been earned.

=IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--(
DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e),
DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR(
DATE(YEAR(HireDate),MONTH(HireDate),DAY(
HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate
-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate),
DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)
--ron

Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after
10 years they get 20 days. I figured if I had the main formula it wouldn't
be to difficult to add the 10+ get 20 days of vacation, but seeing the
formula it might be. I am assuming the As of Date could be now or today or a
set date like the first of the year?

Thanks, I will give this a try.

--
cao


"Ron Rosenfeld" wrote:

On Wed, 4 Feb 2009 13:06:22 -0800, srctr
wrote:

I need to set up a worksheet that will allow me to calculate vacation days
from Hire date as follows: Employee works for 90 days (intro period), then
will accrue 1 vacation day at the beginning of the first of the following
month and one additional vacation day for each month after for a maximum of
10 days per year.

Thereafter they receive 10 days on January 1st after their
first 12 months of service. 4th thru 9th they will recieve 15 days on
January 1st.

Does this mean that they receive 10 days on Jan 1 for years 1,2 and 3; and then
15 days on Jan 1 after that?

What happens during the 10th year?

Hopefully there is a simpler formula, but this seems to work for the parameters
you've mentioned.

The two relevant dates are the "HireDate" and the "AsOfDate" The latter is
the date being evaluated for how much vacation has been earned.

=IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--(
DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e),
DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR(
DATE(YEAR(HireDate),MONTH(HireDate),DAY(
HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate
-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate),
DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)
--ron

I am having trouble with the formula. When I paste it into the cell I get an
error "The formula you typed as an error" message on the (Year which is
directly following AsOfDate+1-DAY(AsOfDate),DATE(YEAR(

??
--
cao


"srctr" wrote:

Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after
10 years they get 20 days. I figured if I had the main formula it wouldn't
be to difficult to add the 10+ get 20 days of vacation, but seeing the
formula it might be. I am assuming the As of Date could be now or today or a
set date like the first of the year?

Thanks, I will give this a try.

--
cao


"Ron Rosenfeld" wrote:

On Wed, 4 Feb 2009 13:06:22 -0800, srctr
wrote:

I need to set up a worksheet that will allow me to calculate vacation days
from Hire date as follows: Employee works for 90 days (intro period), then
will accrue 1 vacation day at the beginning of the first of the following
month and one additional vacation day for each month after for a maximum of
10 days per year.

Thereafter they receive 10 days on January 1st after their
first 12 months of service. 4th thru 9th they will recieve 15 days on
January 1st.

Does this mean that they receive 10 days on Jan 1 for years 1,2 and 3; and then
15 days on Jan 1 after that?

What happens during the 10th year?

Hopefully there is a simpler formula, but this seems to work for the parameters
you've mentioned.

The two relevant dates are the "HireDate" and the "AsOfDate" The latter is
the date being evaluated for how much vacation has been earned.

=IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--(
DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e),
DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR(
DATE(YEAR(HireDate),MONTH(HireDate),DAY(
HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate
-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate),
DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)
--ron

On Thu, 5 Feb 2009 08:13:00 -0800, srctr
wrote:

I am having trouble with the formula. When I paste it into the cell I get an
error "The formula you typed as an error" message on the (Year which is
directly following AsOfDate+1-DAY(AsOfDate),DATE(YEAR(

??
--
cao


Some newsgroup readers -- and Google Groups is notorious for this -- put extra
formatting symbols within posts. Usually not a problem but can really screw up
long formulas.

Try removing the line feeds at the end of each line after you've pasted in the
formula.

And try pasting the formula into the formula bar rather than into the cell.
--ron

On Thu, 5 Feb 2009 07:32:32 -0800, srctr
wrote:

Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after
10 years they get 20 days. I figured if I had the main formula it wouldn't
be to difficult to add the 10+ get 20 days of vacation, but seeing the
formula it might be.

I am assuming the As of Date could be now or today or a
set date like the first of the year?

Yes. I'd just put it into a cell and NAME the cell.

Here's the modification for the 10+years:

=IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--(
DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e),
DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR(
DATE(YEAR(HireDate),MONTH(HireDate),DAY(
HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate
-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate),
DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)+
MAX(0,(YEAR(AsOfDate)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-9)*5


--ron

I was already pasting into the Formula Bar. I had cleared out all line feeds
(I thought). I also tried copying into Word as Unformatted Text. I tried
this again but made sure I deleted the line feeds. Still problem. I guess I
will have to manually type it in
--
cao


"Ron Rosenfeld" wrote:

On Thu, 5 Feb 2009 08:13:00 -0800, srctr
wrote:

I am having trouble with the formula. When I paste it into the cell I get an
error "The formula you typed as an error" message on the (Year which is
directly following AsOfDate+1-DAY(AsOfDate),DATE(YEAR(

??
--
cao


Some newsgroup readers -- and Google Groups is notorious for this -- put extra
formatting symbols within posts. Usually not a problem but can really screw up
long formulas.

Try removing the line feeds at the end of each line after you've pasted in the
formula.

And try pasting the formula into the formula bar rather than into the cell.
--ron

On Thu, 5 Feb 2009 11:40:45 -0800, srctr
wrote:

I was already pasting into the Formula Bar. I had cleared out all line feeds
(I thought). I also tried copying into Word as Unformatted Text. I tried
this again but made sure I deleted the line feeds. Still problem. I guess I
will have to manually type it in
--
cao

Something funny going on as I had no problem copy/pasting it in from what I had
posted on the NG.

I don't believe there is a length problem, as the point where you are getting
the error is well under the maximum allowed formula length, which for XL2003 is
1024 characters, and I believe it is the same back to XL97.
--ron

It still doesn't work, It gives me an error "the formula you typed has an
error". Should I have the cell formatted already a certain way?

--
cao


"Ron Rosenfeld" wrote:

On Thu, 5 Feb 2009 07:32:32 -0800, srctr
wrote:

Yes they get 10 days for years 1, 2, 3; 15 days for years 4 thru 9 and after
10 years they get 20 days. I figured if I had the main formula it wouldn't
be to difficult to add the 10+ get 20 days of vacation, but seeing the
formula it might be.

I am assuming the As of Date could be now or today or a
set date like the first of the year?

Yes. I'd just put it into a cell and NAME the cell.

Here's the modification for the 10+years:

=IF((HireDate+90)AsOfDate,0,MIN(10,SUMPRODUCT(--(
DAY(ROW(INDIRECT(DATE(YEAR(HireDate),MONTH(HireDat e),
DAY(HireDate)+90)&":"&MIN(AsOfDate+1-DAY(AsOfDate),DATE(YEAR(
DATE(YEAR(HireDate),MONTH(HireDate),DAY(
HireDate)+90)),12,31)))))=1)))+(YEAR(AsOfDate)-YEAR(MIN(AsOfDate
-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),MONTH( HireDate),
DAY(HireDate)+90)),12,31))))*10+MAX(0,(YEAR(AsOfDa te)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-3)*5)+
MAX(0,(YEAR(AsOfDate)-YEAR(MIN(
AsOfDate-DAY(AsOfDate),DATE(YEAR(DATE(YEAR(HireDate),
MONTH(HireDate),DAY(HireDate)+90)),12,31))))-9)*5


--ron

On Tue, 10 Feb 2009 16:39:00 -0800, srctr
wrote:

It still doesn't work, It gives me an error "the formula you typed has an
error". Should I have the cell formatted already a certain way?

--
cao

What version of Excel do you have?

It should work in 2007.

It won't work in earlier versions because of nesting limitations.
--ron

On Tue, 10 Feb 2009 20:41:44 -0500, Ron Rosenfeld
wrote:

On Tue, 10 Feb 2009 16:39:00 -0800, srctr
wrote:

It still doesn't work, It gives me an error "the formula you typed has an
error". Should I have the cell formatted already a certain way?

--
cao

What version of Excel do you have?

It should work in 2007.

It won't work in earlier versions because of nesting limitations.
--ron

Assuming you have an earlier version of Excel, I think the simplest method
would be to use VBA and a UDF (user defined function).

Also, some further testing reveals that the method I proposed doesn't always
calculate correctly.



However, your specifications lead to some largish differences. And I wanted to
be sure they were correct before proceeding.

For example, assume we are evaluating someone today 10 Feb 2009

If hired on 3 Jan 2005 he will have accrued 40 days

Hire date + 90 days -- 3 Apr 2005
10 monthly days accrued
Hire date + 12 months -- 3 Jan 2006
Accrual start 1 Jan 2007 so another 10/yr for 3 years.

But if hired on 31 Dec 2004 his hire date + 12 months is now 12/31/05 so he
will start his annual accruals a year earlier, and will have 55 vacation days
by today.

Is this correct?
--ron

On Tue, 10 Feb 2009 23:22:52 -0500, Ron Rosenfeld
wrote:

On Tue, 10 Feb 2009 20:41:44 -0500, Ron Rosenfeld
wrote:

On Tue, 10 Feb 2009 16:39:00 -0800, srctr
wrote:

It still doesn't work, It gives me an error "the formula you typed has an
error". Should I have the cell formatted already a certain way?

--
cao

What version of Excel do you have?

It should work in 2007.

It won't work in earlier versions because of nesting limitations.
--ron

Assuming you have an earlier version of Excel, I think the simplest method
would be to use VBA and a UDF (user defined function).

Also, some further testing reveals that the method I proposed doesn't always
calculate correctly.



However, your specifications lead to some largish differences. And I wanted to
be sure they were correct before proceeding.

For example, assume we are evaluating someone today 10 Feb 2009

If hired on 3 Jan 2005 he will have accrued 40 days

Hire date + 90 days -- 3 Apr 2005
10 monthly days accrued
Hire date + 12 months -- 3 Jan 2006
Accrual start 1 Jan 2007 so another 10/yr for 3 years.

But if hired on 31 Dec 2004 his hire date + 12 months is now 12/31/05 so he
will start his annual accruals a year earlier, and will have 55 vacation days
by today.

Is this correct?
--ron

Oops, the person hired 31 dec 2004 will have 54 vacation days.

Here is a method that uses a bunch of helper columns (rather than VBA) to avoid
the nesting limitations of pre-2007 versions of Excel.

Set up your sheet as follows:

Set up a table and NAME it VacTbl. It should look like:

Yrs Accrued Multiplier
0 0 10
3 30 15
9 120 20

To NAME the table, select the table, then select the Define Name menu option
and enter VacTbl in the NAME box.

------------------
Then enter the following:

$A$1: AsOfDate
$B$1: HireDate
$C$1: HireDate+90d
$D$1: HireDate+12m
$E$1: Yr1Accrual
$F$1: Months
$G$1: Years
$H$1: Accrued Vac

A2: Date to be evaluated
B2: Date Hired
C2: =B2+90
D2: =MIN(DATE(YEAR(B2),MONTH(B2)+{12,13},DAY(B2)*{1,0} ))
E2: =DATE(YEAR(D2-1)+1,1,1)
F2: =SUMPRODUCT(--(DAY(ROW(INDIRECT(MIN(A2,C2)&":"&MIN(A2,E2-1))))=1))
G2:
=SUMPRODUCT(--(DAY(ROW(INDIRECT(MIN(E2,A2)&":"&A2)))*MONTH(ROW(I NDIRECT(MIN(E2,A2)&":"&A2)))=1))

H2:
=MIN(10,F2)+VLOOKUP(G2,VacTbl,2)+(G2-VLOOKUP(G2,VacTbl,1))*VLOOKUP(G2,VacTbl,3)

-------------------------

You can hide columns C:G if you want, once you confirm that the calculations
are proper.

--------------------------
Notes:

The above can be done using a UDF in Visual Basic, but this algorithm should
run faster as spreadsheet functions (mostly because of the array calculations),
and that might make a difference if you have lots of employees.

In versions of Excel prior to 2007, this method will not handle dates that are
after 5 June 2079. Hopefully, by then, if this is still being used, the Excel
version will have been upgraded.
--ron

I will try this. I have 2003 but the company who is using this has 2007.
But I like the spreadsheet option.

--
cao


"Ron Rosenfeld" wrote:

On Tue, 10 Feb 2009 23:22:52 -0500, Ron Rosenfeld
wrote:

On Tue, 10 Feb 2009 20:41:44 -0500, Ron Rosenfeld
wrote:

On Tue, 10 Feb 2009 16:39:00 -0800, srctr
wrote:

It still doesn't work, It gives me an error "the formula you typed has an
error". Should I have the cell formatted already a certain way?

--
cao

What version of Excel do you have?

It should work in 2007.

It won't work in earlier versions because of nesting limitations.
--ron

Assuming you have an earlier version of Excel, I think the simplest method
would be to use VBA and a UDF (user defined function).

Also, some further testing reveals that the method I proposed doesn't always
calculate correctly.



However, your specifications lead to some largish differences. And I wanted to
be sure they were correct before proceeding.

For example, assume we are evaluating someone today 10 Feb 2009

If hired on 3 Jan 2005 he will have accrued 40 days

Hire date + 90 days -- 3 Apr 2005
10 monthly days accrued
Hire date + 12 months -- 3 Jan 2006
Accrual start 1 Jan 2007 so another 10/yr for 3 years.

But if hired on 31 Dec 2004 his hire date + 12 months is now 12/31/05 so he
will start his annual accruals a year earlier, and will have 55 vacation days
by today.

Is this correct?
--ron

Oops, the person hired 31 dec 2004 will have 54 vacation days.

Here is a method that uses a bunch of helper columns (rather than VBA) to avoid
the nesting limitations of pre-2007 versions of Excel.

Set up your sheet as follows:

Set up a table and NAME it VacTbl. It should look like:

Yrs Accrued Multiplier
0 0 10
3 30 15
9 120 20

To NAME the table, select the table, then select the Define Name menu option
and enter VacTbl in the NAME box.

------------------
Then enter the following:

$A$1: AsOfDate
$B$1: HireDate
$C$1: HireDate+90d
$D$1: HireDate+12m
$E$1: Yr1Accrual
$F$1: Months
$G$1: Years
$H$1: Accrued Vac

A2: Date to be evaluated
B2: Date Hired
C2: =B2+90
D2: =MIN(DATE(YEAR(B2),MONTH(B2)+{12,13},DAY(B2)*{1,0} ))
E2: =DATE(YEAR(D2-1)+1,1,1)
F2: =SUMPRODUCT(--(DAY(ROW(INDIRECT(MIN(A2,C2)&":"&MIN(A2,E2-1))))=1))
G2:
=SUMPRODUCT(--(DAY(ROW(INDIRECT(MIN(E2,A2)&":"&A2)))*MONTH(ROW(I NDIRECT(MIN(E2,A2)&":"&A2)))=1))

H2:
=MIN(10,F2)+VLOOKUP(G2,VacTbl,2)+(G2-VLOOKUP(G2,VacTbl,1))*VLOOKUP(G2,VacTbl,3)

-------------------------

You can hide columns C:G if you want, once you confirm that the calculations
are proper.

--------------------------
Notes:

The above can be done using a UDF in Visual Basic, but this algorithm should
run faster as spreadsheet functions (mostly because of the array calculations),
and that might make a difference if you have lots of employees.

In versions of Excel prior to 2007, this method will not handle dates that are
after 5 June 2079. Hopefully, by then, if this is still being used, the Excel
version will have been upgraded.
--ron

On Thu, 12 Feb 2009 12:10:15 -0800, srctr
wrote:

I will try this. I have 2003 but the company who is using this has 2007.
But I like the spreadsheet option.

--

Of the methods I've posted, I believe this last spreadsheet version, with the
helper columns, to be the most accurate, robust, and easiest to maintain.

Let me know.
--ron

Okay I think this is working well for the fulltime employees. I have people
who do not work 40 hrs a week, so that get a portion of the 10 days, 15 days
and 20 days of vacation. I noticed in the formula for Accrued Vac you have
Min (10,F2). Is this the minimum number of days that can be carried over
each year?
--
cao


"Ron Rosenfeld" wrote:

On Thu, 12 Feb 2009 12:10:15 -0800, srctr
wrote:

I will try this. I have 2003 but the company who is using this has 2007.
But I like the spreadsheet option.

--

Of the methods I've posted, I believe this last spreadsheet version, with the
helper columns, to be the most accurate, robust, and easiest to maintain.

Let me know.
--ron

Nevermind I figured it out and added a column for my max carryover days and
changed the formula from Min(10,F2) to Min(Cell with Carryover,F2).

Thanks so much for your help
--
cao


"srctr" wrote:

Okay I think this is working well for the fulltime employees. I have people
who do not work 40 hrs a week, so that get a portion of the 10 days, 15 days
and 20 days of vacation. I noticed in the formula for Accrued Vac you have
c. Is this the minimum number of days that can be carried over
each year?
--
cao


"Ron Rosenfeld" wrote:

On Thu, 12 Feb 2009 12:10:15 -0800, srctr
wrote:

I will try this. I have 2003 but the company who is using this has 2007.
But I like the spreadsheet option.

--

Of the methods I've posted, I believe this last spreadsheet version, with the
helper columns, to be the most accurate, robust, and easiest to maintain.

Let me know.
--ron