Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining formulas
I've been trying to combine the following two formulas with no luck.
=IF(AND(E25<DATE(YEAR(1989),7,1),R41+S41-T41200),200,R41+S41-T41) and =IF(AND(E25DATE(YEAR(1989),7,1),R41+S41-T41120),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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining formulas
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-T41200),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-T41200),200,R41+S41-T41) and =IF(AND(E25DATE(YEAR(1989),7,1),R41+S41-T41120),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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining formulas
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-T41200),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-T41200),200,R41+S41-T41) and =IF(AND(E25DATE(YEAR(1989),7,1),R41+S41-T41120),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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining formulas
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-T41200,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(E25DATE(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-T41200),200,R41+S41-T41) and =IF(AND(E25DATE(YEAR(1989),7,1),R41+S41-T41120),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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining formulas | Excel Discussion (Misc queries) | |||
Combining 2 Formulas | Excel Worksheet Functions | |||
Combining two formulas | Excel Worksheet Functions | |||
Combining formulas and results HELP!! | New Users to Excel | |||
combining countif formulas | Excel Worksheet Functions |