MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Time validation in a cell

Posted by Lenny on October 29, 2000 8:53 PM

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;


Where F4 is the data validation cell (activecell)
The 0.010416666666667 is the decimal eq of 15min