Need help writing a complicated formula. Want formula to stay zero until the
criterias are met as explained below. When criterias are met, want formula
to automatically add a 1, 2 or 3 in the cell based on the hire date after 6
months of service.

After 6 months of service, you get one sick day; then three days maximum a
year (January - December). If first year anniversary falls on January -
June you get 3 sick. If it falls on July - Sept you get 2 sick for remainder
of year. If it falls on Oct - Dec you get 1 sick day for remainder of the
year. Sick days do not carry forward to the next year.

Examples

Hire Date: July 1, 2006
Six months of service date: Jan. 1, 2007
Sick leave accumlated after six months: 1
First Year Anniversary Date: July 1, 2007
Sick leave accumlated: 2 more for remainder of 2007

Hire Date: Jan. 1, 2006
Six months of service date: July 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Jan. 1, 2007
Sick leave accumlated: 3 for 2007

Hire Date: Oct. 1, 2006
Six months of service date: April 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Oct. 1, 2007
Sick leave accumlated: 1 for remainer of 2007

Hi

Try
=IF((yearstart-hiredate)182.5,1,0)+
IF(MONTH(hiredate)9,1,IF(MONTH(hiredate)6,2,3))

--
Regards

Roger Govier


"clueless" wrote in message
...
Need help writing a complicated formula. Want formula to stay zero
until the
criterias are met as explained below. When criterias are met, want
formula
to automatically add a 1, 2 or 3 in the cell based on the hire date
after 6
months of service.

After 6 months of service, you get one sick day; then three days
maximum a
year (January - December). If first year anniversary falls on
January -
June you get 3 sick. If it falls on July - Sept you get 2 sick for
remainder
of year. If it falls on Oct - Dec you get 1 sick day for remainder of
the
year. Sick days do not carry forward to the next year.

Examples

Hire Date: July 1, 2006
Six months of service date: Jan. 1, 2007
Sick leave accumlated after six months: 1
First Year Anniversary Date: July 1, 2007
Sick leave accumlated: 2 more for remainder of 2007

Hire Date: Jan. 1, 2006
Six months of service date: July 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Jan. 1, 2007
Sick leave accumlated: 3 for 2007

Hire Date: Oct. 1, 2006
Six months of service date: April 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Oct. 1, 2007
Sick leave accumlated: 1 for remainer of 2007

Thanks for trying. That didn't work. Let me see if I can explain a little
better.

My spreadsheet has the following:
Employee name / Hire Date / Sick Days Earned / Today's Date

1st Year of Employment
Six months after hire date - Earn 1 sick day
When 1 year anniversary date comes around, you get:
* 3 sick days for remainder of year if 1 year anniversary date
falls
between Jan. - June.
* 2 sick days for remainder of year if 1 year anniversary date
falls
between July - Sept.
* 1 sick day for remainder of year if 1 year anniversary date
falls between
Oct. - Dec.

2nd Year of Employment and thereafter
You get 3 sick days a year

"Roger Govier" wrote:

Hi

Try
=IF((yearstart-hiredate)182.5,1,0)+
IF(MONTH(hiredate)9,1,IF(MONTH(hiredate)6,2,3))

--
Regards

Roger Govier


"clueless" wrote in message
...
Need help writing a complicated formula. Want formula to stay zero
until the
criterias are met as explained below. When criterias are met, want
formula
to automatically add a 1, 2 or 3 in the cell based on the hire date
after 6
months of service.

After 6 months of service, you get one sick day; then three days
maximum a
year (January - December). If first year anniversary falls on
January -
June you get 3 sick. If it falls on July - Sept you get 2 sick for
remainder
of year. If it falls on Oct - Dec you get 1 sick day for remainder of
the
year. Sick days do not carry forward to the next year.

Examples

Hire Date: July 1, 2006
Six months of service date: Jan. 1, 2007
Sick leave accumlated after six months: 1
First Year Anniversary Date: July 1, 2007
Sick leave accumlated: 2 more for remainder of 2007

Hire Date: Jan. 1, 2006
Six months of service date: July 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Jan. 1, 2007
Sick leave accumlated: 3 for 2007

Hire Date: Oct. 1, 2006
Six months of service date: April 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Oct. 1, 2007
Sick leave accumlated: 1 for remainer of 2007

Hi
Then try
=IF((yearstart-hiredate)182.5,1,0)+IF(MONTH(hiredate)9,0,IF(MON TH(hiredate)6,1,2))

--
Regards

Roger Govier


"clueless" wrote in message
...
Thanks for trying. That didn't work. Let me see if I can explain a
little
better.

My spreadsheet has the following:
Employee name / Hire Date / Sick Days Earned / Today's Date

1st Year of Employment
Six months after hire date - Earn 1 sick day
When 1 year anniversary date comes around, you get:
* 3 sick days for remainder of year if 1 year anniversary
date
falls
between Jan. - June.
* 2 sick days for remainder of year if 1 year anniversary
date
falls
between July - Sept.
* 1 sick day for remainder of year if 1 year anniversary
date
falls between
Oct. - Dec.

2nd Year of Employment and thereafter
You get 3 sick days a year

"Roger Govier" wrote:

Hi

Try
=IF((yearstart-hiredate)182.5,1,0)+
IF(MONTH(hiredate)9,1,IF(MONTH(hiredate)6,2,3))

--
Regards

Roger Govier


"clueless" wrote in message
...
Need help writing a complicated formula. Want formula to stay zero
until the
criterias are met as explained below. When criterias are met, want
formula
to automatically add a 1, 2 or 3 in the cell based on the hire date
after 6
months of service.

After 6 months of service, you get one sick day; then three days
maximum a
year (January - December). If first year anniversary falls on
January -
June you get 3 sick. If it falls on July - Sept you get 2 sick for
remainder
of year. If it falls on Oct - Dec you get 1 sick day for remainder
of
the
year. Sick days do not carry forward to the next year.

Examples

Hire Date: July 1, 2006
Six months of service date: Jan. 1, 2007
Sick leave accumlated after six months: 1
First Year Anniversary Date: July 1, 2007
Sick leave accumlated: 2 more for remainder of 2007

Hire Date: Jan. 1, 2006
Six months of service date: July 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Jan. 1, 2007
Sick leave accumlated: 3 for 2007

Hire Date: Oct. 1, 2006
Six months of service date: April 1, 2006
Sick leave accumlated after six months: 1
First Year Anniversary Date: Oct. 1, 2007
Sick leave accumlated: 1 for remainer of 2007