time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

andrewkerr84@hotmail.com - 24 Jun 2005 12:52 GMT

can anybody help with time? i want to convert minutes into hours and
minutes. for example i want to convert 7245 minutes into 120h 45m or
120:45, can't divide 7245 by 60 because that gives 120.75 which is no
good to me.
i've looked at the time formats but they don't seem to help, first of
all they won't recognise anything above 24 hours - i guess because
actual clock time doesn't go above this, i'm not looking at clock time
however - i'm working with lengths of time.

would really appreciate some advice.

thanks

andrew

Reply to this Message

JE McGimpsey - 24 Jun 2005 13:00 GMT

XL stores times as fractional days, so to convert integer minutes to an
XL time, the number needs to be divided by (24*60):

A1: 7245
A2: =A1/1440

Format A2 with

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

which will keep the hours from "rolling over" ever 24 hours.

> can anybody help with time? i want to convert minutes into hours and
> minutes. for example i want to convert 7245 minutes into 120h 45m or
[quoted text clipped - 6 lines]
>
> would really appreciate some advice.

Reply to this Message

andrewkerr84@hotmail.com - 24 Jun 2005 14:24 GMT

thanks for that, it worked well. i should have asked before but now
i've got my time in hours and minutes, how do i get it back into just
minutes.
for example if i've got 23 hrs 35 mins as 23:35 how do i convert it
into just minutes i.e. 1415 mins?

thanks

andrew

Reply to this Message

JE McGimpsey - 24 Jun 2005 14:52 GMT

Multiply it by 1440 and format as General or another number format.

> thanks for that, it worked well. i should have asked before but now
> i've got my time in hours and minutes, how do i get it back into just
[quoted text clipped - 5 lines]
>
> andrew

Reply to this Message

Anne Troy - 24 Jun 2005 13:05 GMT

I yoinked this UDF from a reply Ken Snell made to someone else.

Public Function ConvertTimeFractionToHM(dblTimeFraction As Double) As String
Dim intHour As Integer
intHour = Int(dblTimeFraction)
ConvertTimeFractionToHM = CStr(intHour) & ":" & Format((dblTimeFunction -
intHour) * 60, "00")
End Function

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

> can anybody help with time? i want to convert minutes into hours and
> minutes. for example i want to convert 7245 minutes into 120h 45m or
[quoted text clipped - 10 lines]
>
> andrew

Reply to this Message