IF-AND-OR?

[Guest]

We have attendance sheets for each of our employees. On the sheet, we have
an area depicting how much vacation an employee has accrued. As long as
someone has worked for an entire year prior to the beginning of the year,
they will receive 80 hours (2 weeks) of vacation. So, for example, an
individual who started on 4/23/02, the accrual formula looks like this:

=(IF(AE1>1,MONTH(Y1)*6.667,AE1*6.667)-6.6667)
AE1 contains Years of Service (in this case 4.4)
Y1 contains the current date
6.6667 represents the hours accrued each month (80 hours total will be
accrued)
As of 9/1/06, this individual has accrued 53.34 hours of vacation to be used
in the next calendar year.

The challenge: for those individuals hired in 2005 or later. Someone hired
before the 10th of the month is handled differently than after the 10th of
the month. Example: 9 vacation days earned if hired on or after 3/11 of the
prior year.

Can anyone help?!

 

[PCLIVE]

I think you're going to need to be able to referece the employee's hire
date.

For example:

=IF(HireDate>38352,IF(DAY(38352)<10,"Formula if Hired After 12/31/04 and
hire before the 10th of the month","Formula if Hired After 12/31/04 BUT hire
AFTER the 10th of the month"),"Formula if HireDate is before 1/1/05")



=IF(AND(HireDate>12/31/2004

 

[Guest]

I hope this doesn't sound too dumb, but should I just put the hiredate cell
reference where you have HireDate?

 

[Bob Phillips]

Well sort, but that formula is no use to you as such. It has a hard-coded
date, and you need to add your other formulae.

Just put this in AE1

=DATEDIF(Y2,TODAY(),"Y")+(DATEDIF(Y2,TODAY(),"YM")+DAY(Y2<=10))/10

where Y2 is the hire date (change to suit).


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[PCLIVE]

Yes, you need to reference the cell that contains the hire date. However,
there was one more place in my formula that was supposed to do this. This
formula should do what you need.

=IF(A2>38352,IF(DAY(A2)<10,"Formula 1","Formula2"),"Formula3")

- Formula1 is your Formula when the Hire Date is After 12/31/04 and BEFORE
the 10th of the month
- Formula2 is your Formula when the Hire Date is After 12/31/04 and ON or
AFTER the 10th of the month
- Formula3 is your Formula when the Hire Date was before 1/1/05

HTH,
Paul

 

[PCLIVE]

One last thing. In the formula below, A2 is the hire date.