Time validation in a cell
Posted by Lenny on October 29, 2000 8:53 PM
Hello,
I have a spreadsheet in excel 97 in which I would like to put some sort of validation on it so that only times rounded to the nearest quarter hour can be input. Example, 2:00pm, 2:15pm, 2:30pm, 2:45pm, 3:00pm, etc... If somewhere were to put in a time anything other than a quarter hour, i.e., 2:10pm, then the cell validation would kick in saying only full 1/4 hours are allowed.
Thanks for any help.
Posted by Ivan Moala on October 29, 2000 11:16 PM
The custom formula you want is something like this;
=(ROUNDUP(F4/0.0104166666666667,5)-INT(ROUNDUP(F4/0.0104166666666667,5))=0)
Where F4 is the data validation cell (activecell)
The 0.010416666666667 is the decimal eq of 15min
intervals.
Ivan
