excel question

[jimotis4heisman]

id like to format something in excel.

basically the inputs are for expample 200 minutes.

I would like to format so it says 3hr 20min

greenies to the first person to know how to do it an explain it in a way i can understand...

 

[BuckeyeSkins]

I found this link on the web and thought I'd let you take a look at it instead of me trying to explain it to you. Hope it helps.

Excel Function - Excel CONVERT Function - Excel 2007 CONVERT Function

 

[IronBuckI]

enter 0:200
right-click
format cells
number tab
time
37:30:55


Any number off minutes entered in as 0:{number of minutes} will give you an hours and minutes result.

 

[scooter1369]

Closest thing I can come up with is to use the formula A1/1440 and set the field to hh:mm

Put the formula in the field you want the hh:mmm to appear and enter the total minutes in the field represented in the formula.

so for formula A1/1440, enter 200 in A1 and format the cell with the formula to hh:mm it will show 3:20

 

[jimotis4heisman]

that's what i thought, what if it is greater than 24 hours?

 

[IronBuckI]

that's what i thought, what if it is greater than 24 hours?

scooter's formula with the format set for 37:30:55 will give you 3:20:00 with 200 minutes as the input and 33:20:00 with 200 minutes as the input.

 

[jimotis4heisman]

ok i over simplified and created more issues

how do i make 2,000,000 mins read out in hours:mins?

 

[scooter1369]

ok i over simplified and created more issues

how do i make 2,000,000 mins read out in hours:mins?

use my formula

 

[jimotis4heisman]

use my formula

the result is 21:20

 

[IronBuckI]

ok i over simplified and created more issues

how do i make 2,000,000 mins read out in hours:mins?

use my formula

and my format.

result will be 33333:20:00

 

[scooter1369]

I get 33333:20:00

 

[scooter1369]

use my formula

and my format.

What he said

 

[shetuck]

Try this excel formula:

=CONCATENATE((HOUR((A5/24/60))),"hr ",(MINUTE(A5/24/60)),"min")

The number of minutes (e.g. 200) is in cell A5 (or whatever other cell).

If you put 200 in A5, what the formular yields is: "3hr 20min".

EDIT: I think you'll be able to use this formula without any special field/cell formatting.

 

[shetuck]

When I use the formula I gave above on 2,000,000 minutes, I get this:

"21hr 20min".

 

[scooter1369]

Try this excel formula:

=CONCATENATE((HOUR((A5/24/60))),"hr ",(MINUTE(A5/24/60)),"min")

The number of minutes (e.g. 200) is in cell A5 (or whatever other cell).

If you put 200 in A5, what the formular yields is: "3hr 20min".

EDIT: I think you'll be able to use this formula without any special field/cell formatting.

fucking brilliant