Converting minutes to hours:minutes

[DubboPete]

Hi all,

Not much on the discussion group on this one, so here goes.

In a query, I have field [minutes]. I tried to create an expression
([duration]) with this format, to return hours and minutes:

Duration: Format([minutes],"h:nn")

but all I get is 0:00!

Any help anyone?

Pete

 

[John W. Vinson]

Hi all,

Not much on the discussion group on this one, so here goes.

In a query, I have field [minutes]. I tried to create an expression
([duration]) with this format, to return hours and minutes:

Duration: Format([minutes],"h:nn")

but all I get is 0:00!

Any help anyone?

Pete

A Date/Time field - which your Format function expects - is actually in
fractions of a day (0.5 = 12:00 hours).

Try [minutes] \ 60 & Format([minutes] MOD 60, ":00")

or convert the minutes to a Date/Time datatype:

TimeSerial(0, [Minutes], 0)

 

[Tom van Stiphout]

On Tue, 6 Oct 2009 19:59:37 -0700 (PDT), DubboPete

You expected to convert for example 95 to "1:35". That's not how
Format works. If you read the help page on Format, you will find out
that the number you are providing is interpreted as a date as well as
possible. Check this out in the Immediate window:
?Format( 95,"h:nn")
0:00

?Format( 95,"general date")
3/30/1900
(95 days after day 0, which is 12/30/1899 by default)

?Format( 90.123 ,"General Date")
3/30/1900 2:57:07 AM
(now we see that the time portion is a fraction of a number)

To do what you want, you simply count the number of times 60 fits in
your number - that's the hours, and the remainder is the minutes:
?95\60 & ":" & 95 mod 60
1:35

-Tom.
Microsoft Access MVP

 

[DubboPete]

Hi all,

Not much on the discussion group on this one, so here goes.

In a query, I have field [minutes].   I tried to create an expression
([duration]) with this format, to return hours and minutes:

    Duration: Format([minutes],"h:nn")

but all I get is 0:00!

Any help anyone?

Pete

A Date/Time field - which your Format function expects - is actually in
fractions of a day (0.5 = 12:00 hours).

Try [minutes] \ 60 & Format([minutes] MOD 60, ":00")

or convert the minutes to a Date/Time datatype:

TimeSerial(0, [Minutes], 0)

thanks John, does the job!

 

[DubboPete]

On Tue, 6 Oct 2009 19:59:37 -0700 (PDT), DubboPete


You expected to convert for example 95 to "1:35". That's not how
Format works. If you read the help page on Format, you will find out
that the number you are providing is interpreted as a date as well as
possible. Check this out in the Immediate window:
?Format( 95,"h:nn")
0:00

?Format( 95,"general date")
3/30/1900
(95 days after day 0, which is 12/30/1899 by default)

?Format( 90.123 ,"General Date")
3/30/1900 2:57:07 AM
(now we see that the time portion is a fraction of a number)

To do what you want, you simply count the number of times 60 fits in
your number - that's the hours, and the remainder is the minutes:
?95\60 & ":" & 95 mod 60
1:35

-Tom.
Microsoft Access MVP

Hi all,

Not much on the discussion group on this one, so here goes.

In a query, I have field [minutes].   I tried to create an expression
([duration]) with this format, to return hours and minutes:

    Duration: Format([minutes],"h:nn")

but all I get is 0:00!

Any help anyone?

Pete- Hide quoted text -

- Show quoted text -

Thanks also to Tom, for the in-depth explanation. Makes a lot of
sense now!

Pete