trying to add date validation to existing formula

[fingermouse]

Hi,


as per the title, I have created a formula which does the following:

• results in 'pass' if either 'PAS2030:2014' or 'PAS2030:2017' is entered in Q2

• results in 'n/a' - PAS exception' if 'n/a' is entered in Q2

• results in 'fail - invalid' if anything other than the above is entered in Q2

• results in 'fail - blank' if AR2 is blank

This is the formula:

=IF(ISBLANK('Data Area'!AR2),"Fail - Measure Type is blank",IF(ISNA(MATCH('Data Area'!AR2,PAS_Exception_Measures,0)),IF(OR('Data Area'!Q2="PAS2030:2014",'Data Area'!Q2="PAS2030:2017"),"Pass",IF(OR('Data Area'!Q2="N/A"),"N/A - PAS Exception",IF(ISBLANK('Data Area'!Q2),"Fail - PAS is blank","Fail - PAS Invalid")))))

The above works perfectly. However i want to extend the formula by adding date validation but don't know exactly how or where to place it in the formula. This is what I need:

If the user enters 'PAS2030:2014' in Q2 but also enters a date less than 01/06/17 in another cell (AL2) then this results in "Fail - a DOCI after 31/05/17 must use PAS2030:2017"

I'm guessing this part of the formula should be something like this:

=IF(AND(VALUE('Data Area'!AL2)<42887,"Fail - a DOCI after 31/05/17 must use PAS2030:2017"))

But I don't know how to place it into the existing formula. How could this be done? Please help!! Thanks, Cal

 

[Roderick_E]

why not just make the second formula be the first part of the original code?

=IF(AND(VALUE('Data Area'!AL2)<42887,"Fail - a DOCI after 31/05/17 must use PAS2030:2017")),IF(ISBLANK('Data Area'!AR2),"Fail - Measure Type is blank",IF(ISNA(MATCH('Data Area'!AR2,PAS_Exception_Measures,0)),IF(OR('Data Area'!Q2="PAS2030:2014",'Data Area'!Q2="PAS2030:2017"),"Pass",IF(OR('Data Area'!Q2="N/A"),"N/A - PAS Exception",IF(ISBLANK('Data Area'!Q2),"Fail - PAS is blank","Fail - PAS Invalid"))))))

 

[fingermouse]

why not just make the second formula be the first part of the original code?

=IF(AND(VALUE('Data Area'!AL2)<42887,"Fail - a DOCI after 31/05/17 must use PAS2030:2017")),IF(ISBLANK('Data Area'!AR2),"Fail - Measure Type is blank",IF(ISNA(MATCH('Data Area'!AR2,PAS_Exception_Measures,0)),IF(OR('Data Area'!Q2="PAS2030:2014",'Data Area'!Q2="PAS2030:2017"),"Pass",IF(OR('Data Area'!Q2="N/A"),"N/A - PAS Exception",IF(ISBLANK('Data Area'!Q2),"Fail - PAS is blank","Fail - PAS Invalid"))))))

Hi Roderick,

Many thanks for responding to my post - if i cut and paste your solution into the cell i get a 'the formula you typed contains an error'

 

[Roderick_E]

Hi Roderick,

Many thanks for responding to my post - if i cut and paste your solution into the cell i get a 'the formula you typed contains an error'

Dang, sorry. It's difficult to test since you have some named ranges and such. Maybe:
=IF(AND(VALUE('Data Area'!AL2)<42887,"Fail - a DOCI after 31/05/17 must use PAS2030:2017")),IF(ISBLANK('Data Area'!AR2),"Fail - Measure Type is blank",IF(ISNA(MATCH('Data Area'!AR2,PAS_Exception_Measures,0)),IF(OR('Data Area'!Q2="PAS2030:2014",'Data Area'!Q2="PAS2030:2017"),"Pass",IF(OR('Data Area'!Q2="N/A"),"N/A - PAS Exception",IF(ISBLANK('Data Area'!Q2),"Fail - PAS is blank","Fail - PAS Invalid")))))))

 

[Gerald Higgins]

<date(2017,6,1),q2="pas2030:2014"),"fail -="" a="" doci="" after="" 31="" 05="" 17="" ,must="" use="" pas2030:2017","something="" else")
</date(2017,6,1),q2="pas2030:2014"),"fail>

 

[fingermouse]

Hi Roderick,

Im still getting the same error - I think the formula in both your posts are identical?

 

[Roderick_E]

Hi Roderick,

Im still getting the same error - I think the formula in both your posts are identical?

I moved the perens around some. What is the error?

 

[fingermouse]

Just the same error as before, windows popup 'the formula you typed contains an error'

If I click the fx button next to the formula bar, there's a #VALUE! error in the Logical2 argument box

 

[Roderick_E]

Ok, I assumed your second code was tested. It would not have worked. Try this one

=IF(AND(ISNUMBER(AL2),AL2<42887),"Fail - a DOCI after 31/05/17 must use PAS2030:2017",[COLOR=#333333]IF(ISBLANK('Data Area'!AR2),"Fail - Measure Type is blank",IF(ISNA(MATCH('Data Area'!AR2,PAS_Exception_Measures,0)),IF(OR('Data Area'!Q2="PAS2030:2014",'Data Area'!Q2="PAS2030:2017"),"Pass",IF(OR('Data Area'!Q2="N/A"),"N/A - PAS Exception",IF(ISBLANK('Data Area'!Q2),"Fail - PAS is blank","Fail - PAS Invalid")))))[/COLOR])

 

[fingermouse]

Hi Roderick,

Brilliant!! Thanks for taking the time to help me, its really appreciated.

Your formula worked great, many thanks. However, I messed up with the logic (sorry) and realised that I needed date validation which depended on whether the user entered 'PAS2030:2014' or 'PAS2030:2017'.

so i played about with it and came up with this solution....

=IF('Data Area'!Q2="PAS2030:2014",(IF(AND(ISNUMBER('Data Area'!AL2),'Data Area'!AL2>42886),"Fail - a DOCI after 31/05/17 must use PAS2030:2017","Pass")),IF(ISBLANK('Data Area'!AR2),"Fail - Measure Type is blank",IF(ISNA(MATCH('Data Area'!AR2,PAS_Exception_Measures,0)),IF(OR('Data Area'!Q2="PAS2030:2014",'Data Area'!Q2="PAS2030:2017"),"Pass",IF(OR('Data Area'!Q2="N/A"),"N/A - PAS Exception",IF(ISBLANK('Data Area'!Q2),"Fail - PAS is blank","Fail - PAS Invalid"))))))

I wouldn't have managed without your help - thanks again!