Validating time-of-day input

[Phil C]

Hi All

I have several time-of-day input cells formatted as Custom (hh:mm) and I
calculate various time delays (in minutes). It all works fine, but if the
user enters 1400 by mistake (instead of 14:00) the calculation fails and the
input cell is reset to 00:00.

I have tried formatting the time input cells as Time (hh:mm:ss) - I don't
really want to seconds to display - but get the same problem.

For example, I have data validation for C20 set as follows (C14 is a time
input cell also)

Data|Validation|Settings:
Allow: Time
Data: greater than
Start time: =C14

I would have thought that this criteria would check that (a) data input is
indeed a valid time entry (hh:mm) AND (b) that the time entered was greater
(i.e. later) than the time entered in C14. But it doesn't catch the 'input
error'. I also played around entering Allow = Custom (with the cell
formatted as Custom) but this didn't work either. How to force hh:mm format
entry?

Thanks for your help, Phil

 

[Dave Peterson]

Couldn't you specify an end time, too?

23:59:59 (maybe???)

or base it on C14
=C14+TIME(0,15,0)
(15 minutes after C14)