• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Time Validation

Hello Experts!

I am working on a spreadsheet, in which range E:I contains time values.

IE - 1:02:32 (1 hour, 2 minutes, 32 seconds) or 0:01:07 (1 minute, 7 seconds)

All time values can range from just seconds, into hours.  The format of the time is "#:##:##".

What validation rule do I need to apply, in order to force users to type in this format


Thank you in advance for your help!

~ Geekamo
1 Solution
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

format the cell with one of the built-in time formats or the custom format


Now the decimal part of any number that is entered into that cell will be shown as a time value.

Excel stores time as decimals. There is no data validation out of the box that can check if a user has entered the time value 1:20:33 or the corresponding decimal 0.0559375

cheers, teylyn
Tapan PattanaikSenior EngineerCommented:
GeekamoAuthor Commented:
@ teylyn

Thank you for your input!  I appreciate it.

~ Geekamo
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now