Data Validation - Entry limited to x.00 or X.50 decimals

[Phill]

Hi,


I'm working on a timesheet in which users must enter their time in decimals (e.g. 7.50) rather than in hours and minutes (e.g.7:30). To prevent that users enter invalid data, I'm looking for a way to limit the input to fixed decimals (e.g. 7.50 or 6.00 is correct, 7.30 or 6.45 is invalid).


Is this possible in Data Validation ?

The only way I see to do this is by creating a validation list with all possible entries ( 0.50, 1.00, 1.50, 2.00, etc), I hope however that there is a "cleaner" way to do this.


Thanks in advance for your help, much appreciated !


Phill

 

[Luke M]

Use a custom rule of:

=MOD(A2,0.5)=0


Explaination:

MOD takes the first arguement and divides it by 2nd arguement, then returns the remainder. Since you want all of your entries to be evenly divisible by 0.5, this "remainder" should = 0.

 

[Phill]

Great stuff Luke, exact what I was looking for !