Is there a simple formula to convert time in hundredths to minute

[Ron]

I get an exported excel file that has the time in hours/hundredths and I have
to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
I'm doing the conversion but it takes me about 4 passes - is there a simple
way to convert this kind of time. Thank You

 

[Eva]

Can you tell us more about it? I don't understand why 6.18 = 6:11 or 6.15 =
6:09.

 

[Bernie Deitrick]

Ron,

You have a number of options, depending on what you need. The simplest is to format a cell for
time, and in the cell use a formula like

=A2/24

Where A2 has the 6.18 value.

HTH,
Bernie
MS Excel MVP

 

[Fred Smith]

So you want to convert decimal hours to an Excel time? If so, you want:
=a1/24
Format as time.

Regards,
Fred

 

[David Biddulph]

It's pretty simple, Eva.
6.15 is 6 hours and 15 hundredths.
There are 60 minutes in an hour, so 15 hundredths of an hour is 0.15*60 = 9
minutes, hence 6.15 is 6:09

The answer to Ron's question is that the way to do the conversion is =A1/24,
and format the result in a time format to suit your preferences.

 

[Roger Govier]

Hi Ron

Excel stores it's times as fractions of a day (24 hours)
Format the cell as hh:mm
then with 6.15 in cell A1 =A1/24


--
Regards
Roger Govier

I get an exported excel file that has the time in hours/hundredths and I
have
to convert it to hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.
I'm doing the conversion but it takes me about 4 passes - is there a
simple
way to convert this kind of time. Thank You

__________ Information from ESET Smart Security, version of virus
signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Joe User]

I get an exported excel file that has the time in
hours/hundredths and I have to convert it to
hours/minutes - example 6.18 = 6:11, 6.15 = 6:09, etc.

Well, the "simple" answer is: =A1/24

with the Custom format [h]:mm .

But 6.18 will display as 6:10, not 6:11. The reason is: 60*0.18 = 10.8
sec, and generally, Excel truncates seconds when displaying with an hh:mm
format [1].

So if you really want 6:11, you should do:

=ROUND(A1*60,0)/1440

where 1440 is 60*24.

-----
Endnotes

[1] I said that Excel "generally" truncates seconds when displaying hh:mm.
Actually, it sorta-rounds seconds, then truncates. So 6:10:59 (hh:mm:ss) is
displayed as 6:10 (hh:mm), but 6:10:59.51 is displayed as 6:11. I say "sorta
round" because 6:10:59.5 is displayed as 6:10.

 

[Tbazemore]

Roger,
I have attempted using =A1/24, but its not working.... maybe im putting it
in the wrong place? is it possible to elaborate a little more on the "how
to"? I have also been attempting this coversion for my monthly report.

Thanks.

 

[Fred Smith]

You need to change A1 to whatever cell your hours are in. As you did not
specify any cell addresses, it's impossible for us to know where your data
is. So we use A1 as a sample.

If you still need help, tell us where your data is (ie E1, A27:A46), and
we'll give you a specific formula.

Regards,
Fred

 

[Tbazemore]

Okay... turns out I actually have the hours and minutes in cell A1 thru a15.
I actually need the hour/hundredth)... Is there a formula? and if so, for A1
where should I be putting this formula?

Tabatha

 

[Fred Smith]

You're sure making it difficult for people to provide help.

First you say you have decimal hours, and want to convert to a time. Now you
say you have a time, and need the decimal hours. So let's try again:

1. In B1, enter =a1*24
2. Format as a number with two decimal places
2. Copy this formula down to B15

You should now have your results in cells b1:b15. If you don't get the
results you are looking for, it's probably because you have text, rather
than a time. Convert the text to a time (by, for example, using Text To
Columns) in this case.

Regards,
Fred