Convert Time

[Rob via OfficeKB.com]

Small Problem

Working a on project with units and time

1 unit = 5 minutes

I have a spread shee set up like this

Units Taken Standard Time Duration Time Converted

15 5 75 1:15


My problem is the Time Converted column

75 minutes = 1 and 15 minutes (1:15)

I want formula that converts the 75 into 1:15

Duration in Minutes Minutes Converted Into Hours
60 1:00
75 1:15
91 1:31
115 1:55



and so on


Please Help

 

[JE McGimpsey]

XL stores times as fractional days, so to convert integer minutes to a
time, divide by (25 * 60):

A B C D
1 15 5 =A1*B1 =C1/1440

Format D1 as a time. If your times will be over 24 hours, use

Format/Cells/Number/Custom [h]:mm

to keep the time from "rolling over".

 

[Rob via OfficeKB.com]

Actually I found one of your post before and this works just fine

=INT(A1/60) + MOD(A1,60)/100

The problem I have now is the cell shows 1 for one hour instead of 1:00

I tried to format the cell to h:mm but the cell comes up as 0

Thanks for helping me out btw

 

[JE McGimpsey]

That's because in time format 1 is evaluated as a whole day. If you
format the cell with

Format/Cell/Number/Custom [h]:mm

you'll see the 0:00 turn to 24:00.