P
Phil C
Hi guys
An input validation problem. I have a user input list (A1:A6) of
times-of-day (hh:mm), each one of which should be later than the previous.
In the adjacent column (B2:B6) I calculate the elapsed time in minutes
between the first time (in A1) and each of the subsequent ones. I need to
validate this input in two ways:
1. Check that time in A2 is later than A1; that A3 is later than A2, etc..
AND
2. Check that the elapsed times calculated (A2-A1, A3-A1, A4-A1) are in the
range 100-300 minutes.
Doing '1' is straightforward (e.g. for A2: Validation|Allow|Time|Greater
than|=A1), but how do I also test for '2'. I tried to apply validation to
column B (Allow|Decimal|Between|100|300) but this doesn't work. I am trying
to apply validation to cells that are not themselves input cells (contain
formulae and are protected) which is presumably an inappropriate use (?). If
the validation has to be applied to a pure data entry cell, could I replace
the above condition in A2 (say) by a custom formula of the type:
IF(A2<A1) OR (A1-A2)<100 OR (A1-A2)>300 ....then pop up warning message that
would say something like "The time you have entered is either earlier than
the reference time, or the elapsed time is outside the expected range of
100-300 minutes"
If Yes, what is the syntax of the formula? I have tried
IF(OR(A2<A1,A2-A1<100,A2-A1>300)) but this doesn't work. Does Excel not
realise that these are time values, or is that not the problem?
Thanks in advance, Phil
An input validation problem. I have a user input list (A1:A6) of
times-of-day (hh:mm), each one of which should be later than the previous.
In the adjacent column (B2:B6) I calculate the elapsed time in minutes
between the first time (in A1) and each of the subsequent ones. I need to
validate this input in two ways:
1. Check that time in A2 is later than A1; that A3 is later than A2, etc..
AND
2. Check that the elapsed times calculated (A2-A1, A3-A1, A4-A1) are in the
range 100-300 minutes.
Doing '1' is straightforward (e.g. for A2: Validation|Allow|Time|Greater
than|=A1), but how do I also test for '2'. I tried to apply validation to
column B (Allow|Decimal|Between|100|300) but this doesn't work. I am trying
to apply validation to cells that are not themselves input cells (contain
formulae and are protected) which is presumably an inappropriate use (?). If
the validation has to be applied to a pure data entry cell, could I replace
the above condition in A2 (say) by a custom formula of the type:
IF(A2<A1) OR (A1-A2)<100 OR (A1-A2)>300 ....then pop up warning message that
would say something like "The time you have entered is either earlier than
the reference time, or the elapsed time is outside the expected range of
100-300 minutes"
If Yes, what is the syntax of the formula? I have tried
IF(OR(A2<A1,A2-A1<100,A2-A1>300)) but this doesn't work. Does Excel not
realise that these are time values, or is that not the problem?
Thanks in advance, Phil