Convert Time

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

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


In article <[email protected]>,
"Rob via OfficeKB.com" <[email protected]> wrote:

> 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

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

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.


In article <[email protected]>,
"Rob via OfficeKB.com" <[email protected]> wrote:

> 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