R

#### Ron

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

R

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

Ad

E

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

6:09.

6:09.

B

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

F

=a1/24

Format as time.

Regards,

Fred

D

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.

R

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

__________ Information from ESET Smart Security, version of virus signature database 4743 (20100104) __________Ron said: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

The message was checked by ESET Smart Security.

http://www.eset.com

Ad

J

Well, the "simple" answer is: =A1/24Ron said: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.

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.

T

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.

F

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

T

I actually need the hour/hundredth)... Is there a formula? and if so, for A1

where should I be putting this formula?

Tabatha

Ad

F

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

**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.