Custom Validation formula for business rules

[salgud]

I'm putting business rules as validation criteria in a spreadsheet. These
cells contain dates and the rules are:
1. The value in I7 is greater than H7
2. The month of I7 must be the same as H7
3. The year of I7 must be the same as H7
4. I7 can not be more than 21 years later then G7
Excel tells me there's an error in the following formula, which I can't
find:
=and((I7>H7),month(I7)=month(H7),year(I7)=year(H7),i7>(month(G7),day(G7),year(G7)+21))

Got any suggestions?

 

[Lars-Åke Aspelin]

I'm putting business rules as validation criteria in a spreadsheet. These
cells contain dates and the rules are:
1. The value in I7 is greater than H7
2. The month of I7 must be the same as H7
3. The year of I7 must be the same as H7
4. I7 can not be more than 21 years later then G7
Excel tells me there's an error in the following formula, which I can't
find:
=and((I7>H7),month(I7)=month(H7),year(I7)=year(H7),i7>(month(G7),day(G7),year(G7)+21))

Got any suggestions?

Try this formula:

=AND((I7>H7),MONTH(I7)=MONTH(H7),YEAR(I7)=YEAR(H7),I7<=DATE(YEAR(G7)+21,MONTH(G7),DAY(G7)))

Note the change from > to <= in the last comparison.

Hope this helps / Lars-Åke

 

[Patrick Molloy]

the 3rd MONTH has a bracket on the left without having a closing bracket


=AND( (I7>H7) , _
MONTH(I7)=MONTH(H7) , _
YEAR(I7)=YEAR(H7) ,
_
as it is, the last part doesn't make sense

I'm putting business rules as validation criteria in a spreadsheet. These
cells contain dates and the rules are:
1. The value in I7 is greater than H7
2. The month of I7 must be the same as H7
3. The year of I7 must be the same as H7
4. I7 can not be more than 21 years later then G7
Excel tells me there's an error in the following formula, which I can't
find:

=and((I7>H7),month(I7)=month(H7),year(I7)=year(H7),i7> (
month(G7),day(G7),year(G7)+21))

 

[salgud]

I'm putting business rules as validation criteria in a spreadsheet. These
cells contain dates and the rules are:
1. The value in I7 is greater than H7
2. The month of I7 must be the same as H7
3. The year of I7 must be the same as H7
4. I7 can not be more than 21 years later then G7
Excel tells me there's an error in the following formula, which I can't
find:
=and((I7>H7),month(I7)=month(H7),year(I7)=year(H7),i7>(month(G7),day(G7),year(G7)+21))

Got any suggestions?

Thanks to both of you for the help. Got it working great!