If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Validating time-of-day input
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 |
#2
|
|||
|
|||
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) Phil C wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Validating time-of-day input | Phil C | General Discussion | 1 | August 13th, 2004 03:30 PM |
Function to add up total time taken? | Tails | New Users | 1 | August 9th, 2004 07:32 AM |
Excel Formula Help please | Seventh Day is The Sabbath | Worksheet Functions | 3 | May 22nd, 2004 10:54 AM |
Time Extraction | David M | Worksheet Functions | 5 | May 7th, 2004 03:27 PM |
entering time in and time out | paula | Worksheet Functions | 1 | February 25th, 2004 01:16 AM |