Time input validation in Excel

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
I used a Custom validation for cell K1 of:

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 EngineerCommented:
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()
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

VitalyNagyAuthor 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 EngineerCommented:
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 EngineerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.