Time format problem

C

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?
 
N

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
|
 
C

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?
 
B

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]...
 
N

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
|
 
N

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
| >
|
|
 
N

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
|| >
||
||
|
|
 
C

chrisbarber1

You guys are bloody clever! Superb!

THANK YOU

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

Bob Phillips

You're right Niek, even with trailing spaces.

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping Time in Pivots. 2
sum time in hh:mm:ss format 1
Time Conversion issue 5
calculating date/time with blank cells 2
Please help! date showing as 1900/01/00 2
cells with hours, minutes, and seconds 4
Select longest call duration from 2 cells and place in another cel 3
Cell showing date and time - precision error 2

Top