Military time

[RM270]

I have a large list I am working with. One column lists the hours worked as
military time ( 0900-1200, 1330-1540). Is there a way to format the cell to
turn a color, or pop up an error message if the entry does not match the
military time format, as well as the to and from time? I want to be able to
easily spot an entry that may have been typed in wrong, like 130 instead of
1330.

Thanks for any help provided.

 

[GS]

Another way...

Along with the formatting (0000), use DataValidation in the cells to
receive times. Specify 'Whole Numbers', Min:=0, Max:=9.

 

[Pete_UK]

Well, this is quite an old thread, so I doubt if the OP is still
listening.

But, the OP said that the column lists the hours worked, and gave
0900-1200, 1330-1540 and an example. I'm not sure if this is one
example showing hours worked before and after lunch, or two examples
of what might be in the column, with the examples separated by a
comma, but I think the significant thing is that entries like that
will be treated by Excel as text. Thus the comments that you make
regarding formatting, conditional formatting, and data validation are
not relevant to a text entry in the form "time-time" or "time-time,
time-time".

Pete

 

[GS]

Pete_UK expressed precisely :

Well, this is quite an old thread, so I doubt if the OP is still
listening.

But, the OP said that the column lists the hours worked, and gave
0900-1200, 1330-1540 and an example. I'm not sure if this is one
example showing hours worked before and after lunch, or two examples
of what might be in the column, with the examples separated by a
comma, but I think the significant thing is that entries like that
will be treated by Excel as text. Thus the comments that you make
regarding formatting, conditional formatting, and data validation are
not relevant to a text entry in the form "time-time" or "time-time,
time-time".

Pete

Hi Pete,
I do recall the topic but couldn't find it in my listings, and so I was
not able to review it before replying. My comments regarding custom
format and DV are based on assuming the start/stop times are entered
separately in specific cells. I would consider this as normal since I
do a ot of time recorder spreadsheets for clients.

Otherwise, I totally agree with your comments.

 

[GS]

GS wrote on 4/13/2011 :

I would consider this as normal since I do a ot of time recorder spreadsheets
for clients.

Geez.., I keep missing keystrokes! (I have Lou Gehrig's)
The above line should read:

I would consider this as normal since I do a lot of time recorder
spreadsheets for clients.