Time formats in Excel

[Guest]

I am trying to create a spreadsheet which totals times spent on the phone.

I have formatted the cells as [h]:mm:ss.

The problem I have is that if the time I paste in in less than 1 hour then
Excel misses of the leading 0 and formats as such :mm:ss.

When I try to total the columns, it ignores the cells formated as above. If
I manually enter the leading 0, it stays.

How do I display a leading zero, or, add one in if the value I paste in
doesn't have one.

Thanks in advance

 

[Dave Peterson]

If you type in something less than an hour, does the leading 0 show up?

(I couldn't duplicate what you found.)

Maybe it's a problem with what you're pasting--not the format itself.

I am trying to create a spreadsheet which totals times spent on the phone.

I have formatted the cells as [h]:mm:ss.

The problem I have is that if the time I paste in in less than 1 hour then
Excel misses of the leading 0 and formats as such :mm:ss.

When I try to total the columns, it ignores the cells formated as above. If
I manually enter the leading 0, it stays.

How do I display a leading zero, or, add one in if the value I paste in
doesn't have one.

Thanks in advance

 

[Guest]

Dave,

I think you are right. I am copying data from an external source and it
doesn't have the zero to start with.

Is there a way to add one without manually entering in each cell. The
problem I have is that excel will not include these values without the
leading zero.

If you type in something less than an hour, does the leading 0 show up?

(I couldn't duplicate what you found.)

Maybe it's a problem with what you're pasting--not the format itself.

I am trying to create a spreadsheet which totals times spent on the phone.

I have formatted the cells as [h]:mm:ss.

The problem I have is that if the time I paste in in less than 1 hour then
Excel misses of the leading 0 and formats as such :mm:ss.

When I try to total the columns, it ignores the cells formated as above. If
I manually enter the leading 0, it stays.

How do I display a leading zero, or, add one in if the value I paste in
doesn't have one.

Thanks in advance

 

[Dave Peterson]

Maybe you could just paste into the formula bar and then edit there before you
hit enter (nice for one cell at a time).

Chip Pearson has a routine that allows you to enter 6 digits and it'll parse it
to time. That could be changed (depending on the variety of stuff you
copy|paste???).

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

Dave,

I think you are right. I am copying data from an external source and it
doesn't have the zero to start with.

Is there a way to add one without manually entering in each cell. The
problem I have is that excel will not include these values without the
leading zero.

If you type in something less than an hour, does the leading 0 show up?

(I couldn't duplicate what you found.)

Maybe it's a problem with what you're pasting--not the format itself.

I am trying to create a spreadsheet which totals times spent on the phone.

I have formatted the cells as [h]:mm:ss.

The problem I have is that if the time I paste in in less than 1 hour then
Excel misses of the leading 0 and formats as such :mm:ss.

When I try to total the columns, it ignores the cells formated as above. If
I manually enter the leading 0, it stays.

How do I display a leading zero, or, add one in if the value I paste in
doesn't have one.

Thanks in advance