Try to Combining formulas

[ben simpson]

I've been trying to combine the following two formulas with no luck.

=IF(AND(E25<DATE(YEAR(1989),7,1),R41+S41-T41>200),200,R41+S41-T41)
and
=IF(AND(E25>DATE(YEAR(1989),7,1),R41+S41-T41>120),120,R41+S41-T41)

What I need is a formula that says IF E25(a hiring date) was before July
1,1989, a maxium of 200 can be placed in the cell, but, if E25 was after July
1,1989 only a maxium can be placed in the cell, if the maxiums have not been
reached, then R41+S41-T41.

I've tried this several ways, but every time I do, and when I test different
hiring dates, i keep getting the wrong results. Thanks for looking at this
one, in advance....Ben

[Dave Peterson]

If you put =year(1989) in a cell, you'll see that you don't get 1989 back.
Excel sees that 1989 as the number of days from a base date (Dec 31, 1899 for
most wintel users).

So you'll see 1905 returned (1989 days past 12/31/1899).

So maybe just removing those year()'s would fix it:

=IF(AND(E25<DATE(1989,7,1),R41+S41-T41>200),200,R41+S41-T41)


ben simpson wrote:
>
> I've been trying to combine the following two formulas with no luck.
>
> =IF(AND(E25<DATE(YEAR(1989),7,1),R41+S41-T41>200),200,R41+S41-T41)
> and
> =IF(AND(E25>DATE(YEAR(1989),7,1),R41+S41-T41>120),120,R41+S41-T41)
>
> What I need is a formula that says IF E25(a hiring date) was before July
> 1,1989, a maxium of 200 can be placed in the cell, but, if E25 was after July
> 1,1989 only a maxium can be placed in the cell, if the maxiums have not been
> reached, then R41+S41-T41.
>
> I've tried this several ways, but every time I do, and when I test different
> hiring dates, i keep getting the wrong results. Thanks for looking at this
> one, in advance....Ben

--

Dave Peterson

[ben simpson]

Thanks Dave. Didn't even catch it. I modified it and it works great.
Thanks again....Ben

"Dave Peterson" wrote:

> If you put =year(1989) in a cell, you'll see that you don't get 1989 back.
> Excel sees that 1989 as the number of days from a base date (Dec 31, 1899 for
> most wintel users).
>
> So you'll see 1905 returned (1989 days past 12/31/1899).
>
> So maybe just removing those year()'s would fix it:
>
> =IF(AND(E25<DATE(1989,7,1),R41+S41-T41>200),200,R41+S41-T41)
>
>
> ben simpson wrote:
> >
> > I've been trying to combine the following two formulas with no luck.
> >
> > =IF(AND(E25<DATE(YEAR(1989),7,1),R41+S41-T41>200),200,R41+S41-T41)
> > and
> > =IF(AND(E25>DATE(YEAR(1989),7,1),R41+S41-T41>120),120,R41+S41-T41)
> >
> > What I need is a formula that says IF E25(a hiring date) was before July
> > 1,1989, a maxium of 200 can be placed in the cell, but, if E25 was after July
> > 1,1989 only a maxium can be placed in the cell, if the maxiums have not been
> > reached, then R41+S41-T41.
> >
> > I've tried this several ways, but every time I do, and when I test different
> > hiring dates, i keep getting the wrong results. Thanks for looking at this
> > one, in advance....Ben
>
> --
>
> Dave Peterson
>

[Claire]

WHAT YOU NEED TO DO IS FIRST ADD A NEW COLUMN TO YOUR SPREADSHEET THAT
CALCULATES THE R41+S41-T41 AS FOLLOWS:-

=IF(R41+S41-T41>200,200,R41+S41-T41) WE WILL CALL THIS NEW CELL F1

NOW REVISE YOUR FORMULA TO THE FOLLOWING:-

=IF(E25<DATE(1998,6,30),IF(E25>DATE(1998,1,7),200),F1)

THIS SHOULD WORK

CLAIRE

"ben simpson" wrote:

> I've been trying to combine the following two formulas with no luck.
>
> =IF(AND(E25<DATE(YEAR(1989),7,1),R41+S41-T41>200),200,R41+S41-T41)
> and
> =IF(AND(E25>DATE(YEAR(1989),7,1),R41+S41-T41>120),120,R41+S41-T41)
>
> What I need is a formula that says IF E25(a hiring date) was before July
> 1,1989, a maxium of 200 can be placed in the cell, but, if E25 was after July
> 1,1989 only a maxium can be placed in the cell, if the maxiums have not been
> reached, then R41+S41-T41.
>
> I've tried this several ways, but every time I do, and when I test different
> hiring dates, i keep getting the wrong results. Thanks for looking at this
> one, in advance....Ben