Military time
 

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.

Military time
 

Rember that a time is entered as 13:30, not 1330.
--
David Biddulph


RM270 wrote:
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.

Military time
 

RM270 wrote on 02/17/2010 21:50 ET :
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.

First of all you can change the format of the column of data. Use a Custom
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 to
enter
data using a colon ":" excel will return a value between 0 and 1. You
can use conditional formatting to show this as a data entry error.

Under the Home tab, select the Conditional Formatting in the Styles section
and
choose New Rule... Select "Format only cells that contain". In the
"Format only cells with:" area choose: Cell Value, between, 0, 1.
Then select the format botton at the bottom of the popup screen. You can
change
the font color or the fill color to something that will stand out from the
other
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 900
(9am),
it will change color to show an incorrect entry - if you are looking for a
logical or valid range of times in the column. To prevent people from
accidentally entering 0961 for a time, use the conditional format "Use a
formula to determine which cells to format" and the formula (= RIGHT(B2,2)
"59" ) - where you substitute the cell where the time is located
for B2. As you can see, there can be multiple conditional formats for each
cell.

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


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.

Military time
 

Another way...

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Military time
 

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

On Apr 13, 8:49*pm, rolodex wrote:
*RM270 wrote on 02/17/2010 21:50 ET : 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.

*First of all you can change the format of the column of data. *Use a Custom
*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 to
enter
*data using a colon ":" excel will return a value between 0 and 1. *You
*can use conditional formatting to show this as a data entry error.

*Under the Home tab, select the Conditional Formatting in the Styles section
and
*choose New Rule... *Select "Format only cells that contain". *In the
*"Format only cells with:" area choose: Cell Value, between, 0, 1.
*Then select the format botton at the bottom of the popup screen. *You can
change
*the font color or the fill color to something that will stand out from the
other
*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 900
(9am),
*it will change color to show an incorrect entry - if you are looking for a
*logical or valid range of times in the column. *To prevent people from
*accidentally entering 0961 for a time, use the conditional format "Use a
*formula to determine which cells to format" and the formula (= RIGHT(B2,2)
* "59" ) - where you substitute the cell where the time is located
*for B2. *As you can see, there can be multiple conditional formats for each
*cell.

*Other than the ideas that I listed above, I do not know how to prevent *130
from
*seen as being a mistake if both 0130 and 1330 are both valid times that can be
*entered.

*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.

Military time
 

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Military time
 

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc