Excel 2007 : Convert number of minutes to hours and minutes

I am trying to create a spreadsheet showing the time it takes to walk various distances at various speeds. I want the result to be shown as hours and minutes. I can get hours and decimal portions of hours, 90 minutes as 1.5 hours. I want the result in hours and minutes, 90 minues as 1 hr. 30 minutes--1:30.

Is there an easy way to do this?

See attached workbook

There is one problem. If the answer is for example 1 hour, i get this value: 1 hr. -1,33226762955019E-14 minutes. I "Evaluated" the formula in Excel 2007 and the result is correct in the "Evaluate formula" but it still gives me -1,33226762955019E-14 in the cell.

Hello Mike,

If you have the number 90 in A1 then this formula in B1 will convert to 1:30

=A1/1440

format B1 as h:mm

Originally Posted by daddylonglegs

Hello Mike,

If you have the number 90 in A1 then this formula in B1 will convert to 1:30

=A1/1440

format B1 as h:mm

Thanks this was very helpful!!

How about converting 1:20 to minutes? Does anyone know if there is a formula for that?

I am not sure exactly where to format as[m]. I assume format cells, and then ? I am running 2007. Thank you.

Use the value *1440 to see minutes.
Format as general or number

That was what I had already tried, and it did not work. I get a large white cross symbol.

You get the cross after doing what shg posted or what I posted?

After shg. I am not sure how to use your *1440. I tried 1:20*1440, but that did not work either. Will you please walk me through those steps? Thanks~

You need 1:20 in one cell, say A2, and *1440 in the other cell, say B2
So:
A2: 1:20
B2: =A2*1440

Then format B2 into general or numeric

For shg's suggestion, what you do is say have:

A2: 1:20
Then go to format cells, CUSTOM, then remove whatever is in the line and write [m]. Click OK

Yes! Thank you. Why does 1440 work?

the time value in excel is in a day format.
So you *24 to get it into hours, then *60 to get it into minutes

Yeah! Thank you.

Thank you to who ever ask and solved this I have been pulling my hair out. Yes

Brenda

if i have 1939 minutes, have converted into as 1939/1440=1.3
now what to do...

Originally Posted by dip11

You need 1:20 in one cell, say A2, and *1440 in the other cell, say B2
So:
A2: 1:20
B2: =A2*1440

Then format B2 into general or numeric

For shg's suggestion, what you do is say have:

A2: 1:20
Then go to format cells, CUSTOM, then remove whatever is in the line and write [m]. Click OK

but i want total days also

but i am having a problem in converting a number to hours.. for eg 205 to 205:00 hr .. please any help would be nice

thanks, I have been looking for ages. this is great!

thanks i get it

daddylonglegs,

Please tell me how dividing 1440 into minutes produces the result.

Thanks,
Mike

Originally Posted by mikeywhite

Please tell me how dividing 1440 into minutes produces the result.

Excel time is stored as a decimal fraction of a day. There are 1440 minutes in a day: 24*60.

For example, if you enter 1 hr 30 min in the form 1:30 (90 min), the decimal value is 0.0625, and 0.0625*1440 is indeed 90.

(You can see the decimal value of Excel time by formatting the cell as Number.)

Caveat: Because of the way that Excel stores numeric values (64-bit binary floating-point), most decimal fractions cannot be represented exactly. Consequently, if you want an integer number of minutes, it is prudent to explicitly round the calculation to an integer. For example, if the Excel time is in A1, use ROUND(A1*1440,0) to calculate integer minutes instead of simply A1*1440.

Thanks Joe!

Just one addition. If the hrs exceed 24, then go to format cells, CUSTOM, remove whatever is in the Type: box and write [h]:mm .

Note to Admin. Whilst the admin point of not posting on old posts is valid, wouldn't having multiple threads with the same title be even more confusing (in particular post #20)? The title of the thread indicates what it is about, similar questions relating to the same can surely be tolerated at least.

Please don't flame me for this, saying it should go somewhere else as I feel it is particularly relevant to some of the posts on this thread.