Formatting Time As hhmmss Gives Me Dates Instead?

[Donna]

I'm trying to format a column as 6-digit time cells with the format hhmmss so
I can create a time column with the time increasing at regular increments
(for instance, every second or every 15 seconds). The range of values can be
from 000000 (midnight) to 235959 (one second before midnight). Every custom
format I try to create, all of which are variations of hhmmss, insists on
giving me the date with the time, but I've got the date in another column,
and that's where it needs to be.

For example, if I enter 000000, the cell shows 000000, and the formula bar
says 12:00:00 AM. If I enter 000001, the cell shows 000000, and the formula
bar says 1/1/1900 12:00:00 AM. If I enter 000002, the cell shows 000000 and
the formula bar says 1/2/1900, 12:00:00 AM. The date is not only totally
foreign to the hhmmss format, it's worse than useless for what I'm doing.
I'm completely at a loss as to why I only show 000000 no matter what I enter.

My ultimate goal is to concatenate these cells I'm populating with others to
create a long record from each row, so I can't have extraneous colons among
my data. If I use a number format, I can't increment as time -- i.e., after
the 59th second I have one minute instead of 60 seconds. I'm creating these
on the order of ten thousand at a time, so the thought of having to do this
manually is really unattractive.

All help would be greatly appreciated!
Donna

 

[Peo Sjoblom]

You need to enter time so Excel understand it is time, otherwise you need an
event macro. One hour in Excel is 1/24 so when you enter 00001 it is the
equivalent of 24 hours thus the date

http://www.cpearson.com/excel/DateTimeEntry.htm

you cannot simply format a cell and have Excel change the underlying value

 

[Ron Rosenfeld]

I'm trying to format a column as 6-digit time cells with the format hhmmss so
I can create a time column with the time increasing at regular increments
(for instance, every second or every 15 seconds). The range of values can be
from 000000 (midnight) to 235959 (one second before midnight). Every custom
format I try to create, all of which are variations of hhmmss, insists on
giving me the date with the time, but I've got the date in another column,
and that's where it needs to be.

For example, if I enter 000000, the cell shows 000000, and the formula bar
says 12:00:00 AM. If I enter 000001, the cell shows 000000, and the formula
bar says 1/1/1900 12:00:00 AM. If I enter 000002, the cell shows 000000 and
the formula bar says 1/2/1900, 12:00:00 AM. The date is not only totally
foreign to the hhmmss format, it's worse than useless for what I'm doing.
I'm completely at a loss as to why I only show 000000 no matter what I enter.

My ultimate goal is to concatenate these cells I'm populating with others to
create a long record from each row, so I can't have extraneous colons among
my data. If I use a number format, I can't increment as time -- i.e., after
the 59th second I have one minute instead of 60 seconds. I'm creating these
on the order of ten thousand at a time, so the thought of having to do this
manually is really unattractive.

All help would be greatly appreciated!
Donna

Formatting only controls how Excel DISPLAYS the contents of a cell. IT does
not affect how Excel PARSES your entry.

Excel stores dates and times as days and fractions of a day. Day 1 is either
1/1/1900 or 1/1/1904.

If you need to enter times without separators, you will have to use a formula
or VBA routine to parse your entry.

Or you could enter your times as 00:00:00 to 23:59:59


--ron

 

[Donna]

Ah, of course. I knew there was a major concept that was escaping me.

I ended up with this equation, which gave me random times during the day
(86400 being the number of seconds in a day), which suits my purpose even
better than incrementing the time:

=(1/86400)*(rand()*86400)

Then I applied the format, and it worked beautifully.

Thanks, Peo and Ron.