Keeping Leading Zeros; How Please ?

[Robert11]

---- I apologize if this is a duplicate posting; having problems with
my Postings via eternal-September ----

Hello,

This is really driving me crazy; have tried just about everything I
can think of.
Using Excel 2007.

I have some time data in a column.
It's in GMT, and looks like, e.g., 0430 which would be 4:30 AM

All the manipulation which I want to do with the GMT time drops any
leading zeros.

e.g. 0030 would become just 30

Have tried formatting the GMT column as number, general, and text;
nothing works.
Doesn't seem to be any GMT format in Special or Custom.

How can I tell Excel to keep all the leading zeros ?

BTW: not to complicate my question any, but the column of GMT data
came from a spreadsheet that is not mine. The column was formatted as
a zip code. My guess is that the creator also had problems with
leading zeros, and found, possibly, that this was the only format that
allowed
him to show the GMT. My problem is trying to do some manipulation
with the column, and some simple math on it, and still have it show
the leading zeros.

Much thanks,
Bob

 

[James Ravenswood]

---- I apologize if this is a duplicate posting; having problems with
my Postings via eternal-September ----

Hello,

This is really driving me crazy; have tried just about everything I
can think of.
Using Excel 2007.

I have some time data in a column.
It's in GMT, and looks like, e.g., 0430  which would be 4:30 AM

All the manipulation which I want to do with the GMT time drops any
leading zeros.

e.g. 0030 would become just 30

Have tried formatting the GMT column as number, general, and text;
nothing works.
Doesn't seem to be any GMT format in Special or Custom.

How can I tell Excel to keep all the leading zeros ?

BTW: not to complicate my question any, but the column of GMT data
came from a spreadsheet that is not mine.  The column was formatted as
a zip code.  My guess is that the creator also had problems with
leading zeros, and found, possibly, that this was the only format that
allowed
him to show the GMT.  My problem is trying to do some manipulation
with the column, and some simple math on it, and still have it show
the leading zeros.

Much thanks,
Bob

Format cells.... > Number > Custom > 0000

 

[joeu2004]

I have some time data in a column.
It's in GMT, and looks like, e.g., 0430 which would
be 4:30 AM
All the manipulation which I want to do with the GMT
time drops any leading zeros. [....]
The column was formatted as a zip code. My guess is
that the creator also had problems with leading zeros,
and found, possibly, that this was the only format that
allowed him to show the GMT. My problem is trying to
do some manipulation with the column, and some simple
math on it, and still have it show the leading zeros.

If that were your only problem, you could simply format your other columns
or cells, the results of your "manipulations", as Special Zip Code as well.

Of course, formatting as Custom 0000 would be better. That's really all
Special Zip Code is. (Well, it is Custom 00000.)

But note that when you enter time in the form 0430, Excel does __not__
recognize that as 4:30 (hh:mm). Consequently, if you simply add 0531, the
result is __not__ 1001 as you might expect.

If you are aware of that issue and you already take care of it with your
"manipulations", fine.

Otherwise, it might be better to convert the column of original data as
Excel time. You can choose to continue to display it without a colon by
using the format Custom [hh]mm.

To convert the original data, create a parallel column with formulas of the
form =--(INT(A1/100)&":"&MOD(A1,100)) formatted as Time or as Custom [hh]mm.

The double negative causes Excel to interpret the normally text result as
numeric time.