Validation using AND function

  • Thread starter Thread starter grin2000
  • Start date Start date
G

grin2000

I have 2 cells. One with the date 2/1/2004 and one 8/1/2005. The firs
date represents when someone starts training for a new job (2/1/2004)
The second date is the first day of the 19th month which represents th
date when they actually become a full time employee after the 18 ful
months of training. That full 18 months is the most time that perso
should be trained for no longer. So this means that the person coul
start earlier than the 1st day of the 19th month if the training i
completed earlier but no later than that day (8/1/2005).
I need to validate that 2nd date by clicking on that cell and going t
Data and Valuation. Then I have to chose a custom criteria in the for
of a function which does 3 things.
1)To validate that the 8/1/2005 is greater than 2/1/2004. 2)To validat
that the start date (2/1/2004) is on the first day of the month. 3)T
validate that the full time employee status date (8/1/2005) is les
than or equal to the 18 month difference from the start date o
2/1/2004.
The used the AND function for all 3 different checks but I couldn't ge
it to work. I really appreciate it if someone could explain to me ho
this can be done using the AND function.
THANK
 
Data > Validation > Custom, and enter this formula in the box (adjust the
"AND's" to fit your needs)..........

=AND(H1=1,I1=2,J1=3)

Vaya con Dios,
Chuk, CABGx3
 
Hi Grin2000!

With the start date in A1 and date of full time employment in B1, the
following custom formula for validating B1 appears to work OK:

=AND(B1>A1,DAY(A1)=1,B1<=DATE(YEAR(A1),MONTH(A1)+18,DAY(A1)))

However, I don't like it because it rejects an entry in B1 because of
an invalid entry into A1.

I think I'd prefer validating A1 to reject a date that isn't the first
of the month and then validating B1 for the other two conditions.
 
Thanks, Norman!
You're right I used your advise for validating B1 but I would like t
try it your way but how would you validate A1 to reject a date tha
isn't the first of the month
 
Hi Grin2000!

Use custom formula in validation of A1:

=DAY(A1)=1

You should be able to pre-test your validation formulas in an empty
cell:

In (say) D1 I type:

=DAY(A1)=1

To be a valid validation formula it must return TRUE or FALSE.

Once I have tested in an empty cell I select the formula in the
formula bar
Right click Copy [or Ctrl + c]
Select the cell to be validated
Data > Validation > Custom
Point and click the formula box
Ctrl + v [unfortunately right click doesn't do anything]
OK

You can now change the validation for B1 by deleting the condition 2.
 
Back
Top