Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Validation formula for business rules
I'm putting business rules as validation criteria in a spreadsheet. These
cells contain dates and the rules a 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((I7H7),month(I7)=month(H7),year(I7)=year(H7) ,i7(month(G7),day(G7),year(G7)+21)) Got any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Validation formula for business rules
On Tue, 14 Jul 2009 11:25:08 -0600, salgud
wrote: I'm putting business rules as validation criteria in a spreadsheet. These cells contain dates and the rules a 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((I7H7),month(I7)=month(H7),year(I7)=year(H7 ),i7(month(G7),day(G7),year(G7)+21)) Got any suggestions? Try this formula: =AND((I7H7),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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Validation formula for business rules
the 3rd MONTH has a bracket on the left without having a closing bracket
=AND( (I7H7) , _ MONTH(I7)=MONTH(H7) , _ YEAR(I7)=YEAR(H7) , _ this looks wrong I7 MONTH(G7), DAY(G7), YEAR(G7)+21) as it is, the last part doesn't make sense "salgud" wrote in message .. . I'm putting business rules as validation criteria in a spreadsheet. These cells contain dates and the rules a 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((I7H7),month(I7)=month(H7),year(I7)=year(H7) ,i7 ( month(G7),day(G7),year(G7)+21)) Got any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Validation formula for business rules
On Tue, 14 Jul 2009 11:25:08 -0600, salgud wrote:
I'm putting business rules as validation criteria in a spreadsheet. These cells contain dates and the rules a 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((I7H7),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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I set up a validation rules with two data rules | Excel Worksheet Functions | |||
Data Validation Rules | Excel Worksheet Functions | |||
Validation rules base on formula on other cell | Excel Programming | |||
Validation rules | Excel Programming | |||
Exceptions to Validation Rules | Excel Worksheet Functions |