# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Excel 2007 : Convert number of minutes to hours and minutes

## MikeFromIndy

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?

----------


## Steve R

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.

----------


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

----------


## shg

Welcome to the forum.

If A1 contains the time in decimal minutes (e.g., 90), then to see the time in hours and minutes, =A1/1440 and format as h:mm

If A1 contains the time in decimal hours (e.g., 1.5), then to see the time in hours and minutes, =A1/24 and format as h:mm

----------


## Jbramlett

> 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!!   :Smilie:

----------


## AllisonT

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

----------


## shg

You can just format it as [m] to see minutes.

----------


## AllisonT

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

----------


## dip11

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

----------


## shg

Home tab, Format > Format Cells, Number tab, click Custom, and in the Type box, paste [m]

----------


## AllisonT

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

----------


## shg

Large white cross?

----------


## dip11

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

----------


## AllisonT

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~

----------


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

----------


## AllisonT

Yes!  Thank you.  Why does 1440 work?

----------


## dip11

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

----------


## AllisonT

Yeah!  Thank you.

----------


## BrendaLee123

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

Brenda

----------


## sharma_ashish123

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

----------


## arlu1201

Ashish,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## pankaj_s

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



but i want total days also

----------


## arlu1201

pankaj,

Check post 21.

----------


## Gaurangtalati

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

----------


## FDibbins

Gaurangtalati welcome to the forum  :Smilie: 

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## Rose88

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

----------


## Saif.khan.Qs

thanks i get it

----------


## mikeywhite

daddylonglegs,

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

Thanks,
Mike

----------


## joeu2004

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

----------


## mikeywhite

Thanks Joe!

----------


## IanB 23

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.

----------

