Validate to quarter units

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.
 
Hi Shawn,

Not sure of the interpretation of your question. Do you mean that all values
must be less than one be in quarter increments or that the values can include
greater than one but must be in quarter increments.

Use Data validation. Select the range to be validated and enter the formula
as if it applies to the first cell of the selected range. (Excel looks after
applying it correctly to the remaining cells in the selection.)

If the first option above then the following in Data Validation formula.
=AND(MOD(A1,0.25)=0,A1<1)

If the second option then the following Data Validation formula.
=MOD(A1,0.25)=0
 
Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.
Click to expand...

Data/Validation/Settings

Custom
Formula: =MOD(A1,0.25)=0

--ron
 
The cells can be less than or greater than 1, but would never be greater than
24 or less than 0. This is on a time sheet and we caputre time to the
quarter unit. So a formula that would validate greater than 0, less than
24.00001 and to the quarter unit would be idea.
 
I got the following to do just what I wanted (kicks out less than 0, more
than 24, and anything that isn't on a quarter unit).

=IF(MOD(L1,0.25)<>0,FALSE,IF(L1<0,FALSE,IF(L1>24,FALSE,TRUE)))
 
I got the following to do just what I wanted (kicks out less than 0, more
than 24, and anything that isn't on a quarter unit).

=IF(MOD(L1,0.25)<>0,FALSE,IF(L1<0,FALSE,IF(L1>24,FALSE,TRUE)))
Click to expand...

Glad to help Shawn.

But adding the 0-24 constraint, this is a bit shorter:

=AND(A1>=0,A1<=24,MOD(A1,0.25)=0)

--ron
 
Back
Top