Time Format: Minutes showing up as Hours

[wfmguy]

Type: Excel Question
Sub Type: Time Format Issue
Description: I am extracting data from this tool, and I am getting this error, where Minutes are Showing up as Hours.

They are not being converted. They are literally being moved to hours.

Example:

Value - 0:10:00 = 10 Minutes
It is showing as :
10:00:00 = 10 hours

Example 2:

Value - 00:07:00 = 7 minutes
It is showing as :
07:00:00 = 7 Hours

There are 4000+ such entries.
is there a way I can fix this?

thanks a lot in advance.

 

[Peter_SSs]

Re: Excel Question: Time Format: Minutes showing up as Hours

Welcome to the MrExcel board!

Would this work for you?
Put the formula shown in an adjacent column & copy down.
Copy the column of formulas & PasteSpecial -> Values
Format the column with the time format you want.

Book1
	A	B
1	10:00:00	0:10:00
2
3	7:00:00	0:07:00
Sheet2 (5)

Cell Formulas
Range	Formula
B1	=IF(A1="","",A1/60)

 

[oldbrewer]

Re: Excel Question: Time Format: Minutes showing up as Hours

[TABLE="width: 549"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]0:10:00[/TD]
[TD="align: right"]00:10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]I merely multiplied A1 by 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]I entered A1 as text to force the single leading zero[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wfmguy]

Re: Excel Question: Time Format: Minutes showing up as Hours

Welcome to the MrExcel board!

Would this work for you?
Put the formula shown in an adjacent column & copy down.
Copy the column of formulas & PasteSpecial -> Values
Format the column with the time format you want.

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10:00:00[/TD]
[TD="align: right"]0:10:00[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]7:00:00[/TD]
[TD="align: right"]0:07:00[/TD]

</tbody>

Sheet2 (5)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(A1="","",A1/60)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for this.
It worked!

Can you please also explain what was happening and what you did to fix it?

 

[Peter_SSs]

Re: Excel Question: Time Format: Minutes showing up as Hours

Thank you for this.
It worked!

Can you please also explain what was happening and what you did to fix it?

Good news, thanks for letting us know.

I'm not sure why it happened in the first place, but presumably something to do with how the data was coming from the other software tool. However, since you had hours and wanted minutes and there are 60 minutes in an hour, I just divided by 60 to convert.