Time format problem

[chrisbarber1]

I pasted a column of times that were in this format:

31:50 mm:ss


They appear on my sheet as:

31:50:00 and when the cell is selected 01/01/1900 07:50:00


I need to change in to format:

00:31:50 hh:mm:ss


Can anyone please help?

 

[Niek Otten]

=A1/60, format as hh:mm:ss

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| I pasted a column of times that were in this format:
|
| 31:50 mm:ss
|
|
| They appear on my sheet as:
|
| 31:50:00 and when the cell is selected 01/01/1900 07:50:00
|
|
| I need to change in to format:
|
| 00:31:50 hh:mm:ss
|
|
| Can anyone please help?
|
|
| --
| chrisbarber1
| ------------------------------------------------------------------------
| chrisbarber1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37130
| View this thread: http://www.excelforum.com/showthread.php?threadid=568616
|

 

[chrisbarber1]

This doesn't seem to work.

The problem is it thinks
33:40:00 is hh:mm:ss
however the numbers represent mm:ss and I can't get rid of the extra
00

Ideally I need to knock of the extra zeros and add two to the
beginnning
for example to turn 33:40:00 in to 00:33:40

How can we achieve this?

 

[Bob Phillips]

As Niek suggested, divide by 60. It's either in hh:mm format, so divide by
60, or text, in which case, edit, don't change, and then see what you have.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"chrisbarber1" <[email protected]>
wrote in message
news:[email protected]...

 

[Niek Otten]

By doing what I suggested. Did you try?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| This doesn't seem to work.
|
| The problem is it thinks
| 33:40:00 is hh:mm:ss
| however the numbers represent mm:ss and I can't get rid of the extra
| 00
|
| Ideally I need to knock of the extra zeros and add two to the
| beginnning
| for example to turn 33:40:00 in to 00:33:40
|
| How can we achieve this?
|
|
| --
| chrisbarber1
| ------------------------------------------------------------------------
| chrisbarber1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37130
| View this thread: http://www.excelforum.com/showthread.php?threadid=568616
|

 

[Niek Otten]

<or text>

Ah! Didn't think about that! Very likely this is the case!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| As Niek suggested, divide by 60. It's either in hh:mm format, so divide by
| 60, or text, in which case, edit, don't change, and then see what you have.
|
| --
| HTH
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "chrisbarber1" <[email protected]>
| wrote in message
| | >
| > This doesn't seem to work.
| >
| > The problem is it thinks
| > 33:40:00 is hh:mm:ss
| > however the numbers represent mm:ss and I can't get rid of the extra
| > 00
| >
| > Ideally I need to knock of the extra zeros and add two to the
| > beginnning
| > for example to turn 33:40:00 in to 00:33:40
| >
| > How can we achieve this?
| >
| >
| > --
| > chrisbarber1
| > ------------------------------------------------------------------------
| > chrisbarber1's Profile:
| http://www.excelforum.com/member.php?action=getinfo&userid=37130
| > View this thread: http://www.excelforum.com/showthread.php?threadid=568616
| >
|
|

 

[Niek Otten]

But even then it should work, isn't it?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| <or text>
|
| Ah! Didn't think about that! Very likely this is the case!
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| As Niek suggested, divide by 60. It's either in hh:mm format, so divide by
|| 60, or text, in which case, edit, don't change, and then see what you have.
||
|| --
|| HTH
||
|| Bob Phillips
||
|| (replace somewhere in email address with gmail if mailing direct)
||
|| "chrisbarber1" <[email protected]>
|| wrote in message
|| || >
|| > This doesn't seem to work.
|| >
|| > The problem is it thinks
|| > 33:40:00 is hh:mm:ss
|| > however the numbers represent mm:ss and I can't get rid of the extra
|| > 00
|| >
|| > Ideally I need to knock of the extra zeros and add two to the
|| > beginnning
|| > for example to turn 33:40:00 in to 00:33:40
|| >
|| > How can we achieve this?
|| >
|| >
|| > --
|| > chrisbarber1
|| > ------------------------------------------------------------------------
|| > chrisbarber1's Profile:
|| http://www.excelforum.com/member.php?action=getinfo&userid=37130
|| > View this thread: http://www.excelforum.com/showthread.php?threadid=568616
|| >
||
||
|
|

 

[chrisbarber1]

You guys are bloody clever! Superb!

THANK YOU

I thought I has going to have to individually edit 10, 000 rows

 

[Bob Phillips]

You're right Niek, even with trailing spaces.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)