Time input validation in Excel

VitalyNagy
VitalyNagy used Ask the Experts™
on
How to set a validation rule to allow input of a time within, say +/-5 min of the current system time? (I know that one can ensure that with Shift-Ctrl+:, but nonetheless. Excel 2007.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015
Commented:
You can do this...Select your cell where you want to limit this... Then go to

Data-->Validation-->Select time-->Between-->

Start time-->=NOW()-TIME(,5,)

End Time-->==NOW()+TIME(,5,)

Hit okay and now that cell will only accept +-5 mins of your current system time only...

Saurabh...
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I used a Custom validation for cell K1 of:
=ABS(K1-NOW()+TODAY())<5/1440

I found it necessary to subtract TODAY from NOW to get the validation to accept Shift + CTRL + :
Note that afternoon times must be entered using a military clock (e.g. 15:37), or else you must include the PM
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you test Saurabh's suggestion, make sure you subtract TODAY(). Otherwise, it fails to accept valid time entries.

Start time-->        =NOW()-TIME(,5,)-TODAY()

End Time-->         =NOW()+TIME(,5,)-TODAY()
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
The advice by Byundt is preferable as it accepts the shortcut. Thank you. Now, how can I spread this Custom validation over a range of cells without typing the cell address in each new cell?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If time validation is important in a five minute period before or after midnight, it is possible that the modified version of Saurabh's suggestion may work better than m Custom validation formula. At all other times of the day, I believe the two approaches are equivalent.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you specify the address of the top left cell in a block of cells that use the same validation, it will be updated for all other cells that use the validation. Or you can copy the validation from one cell to another, and it will update the cell reference automatically.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial