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.

 

[rolodex]

RM270 wrote on 02/17/2010 21:50 ET

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

Thanks for any help provided

First of all you can change the format of the column of data. Use a Custo
format of 0000 and this will force all data to be a minimum of 4-digits long.
If 200 is entered, it will default to 0200 (or 2am). If somebody tries t
ente
data using a colon ":" excel will return a value between 0 and 1. Yo
can use conditional formatting to show this as a data entry error

Under the Home tab, select the Conditional Formatting in the Styles sectio
an
choose New Rule... Select "Format only cells that contain". In th
"Format only cells with:" area choose: Cell Value, between, 0, 1.
Then select the format botton at the bottom of the popup screen. You ca
chang
the font color or the fill color to something that will stand out from th
othe
data. Choose OK to accept the new conditional format rule

You can also create a rule that if the value of the cell is less than 90
(9am)
it will change color to show an incorrect entry - if you are looking for
logical or valid range of times in the column. To prevent people fro
accidentally entering 0961 for a time, use the conditional format "Use
formula to determine which cells to format" and the formula (= RIGHT(B2,2

"59" ) - where you substitute the cell where the time is locate

for B2. As you can see, there can be multiple conditional formats for eac
cell

Other than the ideas that I listed above, I do not know how to prevent 13
fro
seen as being a mistake if both 0130 and 1330 are both valid times that can b
entered


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