Calculating Number of hours & minutes

[Guest]

I am trying to calculate the length of time a room is booked for. I have
used the expression Hours Booked: DateDiff("h",[Start Time],[StopTime]) which
works for whole hours. The problem I have is it only seems to look at the
minutes if the start & stop minutes are the same i.e 09:15 to 12:15. If the
times are 09:30 to 12:00 it returns 3 hours not 2.5 hours. All times are
formatted to Short Time, can anyone help me? Many thanks.

 

[Guest]

I have worked this one out for myself!!

For information it should be Hours Booked: (DateDiff("n",[Start
Time],[StopTime])/60)
But if anyone has better way I would still like to here it.
Now I just need to get the report to pick up the Hours Booked answer as its
not part of a table, so I might need to get back for help on this.

Many thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How 'bout:

Get seconds between times:

? DateDiff("s", [Start Time], [StopTime])
9252 sec
? 9252 \ 3600
2 hrs
? ((9252 / 3600) - (9252 \ 3600)) * 60
34.2 mins
? int(34.2)
34

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd8IzYechKqOuFEgEQIywwCgy+Zsin+CIWX/kwkypvXsSBYvexAAn2Yd
Cgz4fmkmXfeCm4Itt7pyeFJt
=ZEJh
-----END PGP SIGNATURE-----

 

[Guest]

Many thanks for your quick reply. I have not used seconds in the times
booked but thanks anyway.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How 'bout:

Get seconds between times:

? DateDiff("s", [Start Time], [StopTime])
9252 sec
? 9252 \ 3600
2 hrs
? ((9252 / 3600) - (9252 \ 3600)) * 60
34.2 mins
? int(34.2)
34

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd8IzYechKqOuFEgEQIywwCgy+Zsin+CIWX/kwkypvXsSBYvexAAn2Yd
Cgz4fmkmXfeCm4Itt7pyeFJt
=ZEJh
-----END PGP SIGNATURE-----

I am trying to calculate the length of time a room is booked for. I have
used the expression Hours Booked: DateDiff("h",[Start Time],[StopTime]) which
works for whole hours. The problem I have is it only seems to look at the
minutes if the start & stop minutes are the same i.e 09:15 to 12:15. If the
times are 09:30 to 12:00 it returns 3 hours not 2.5 hours. All times are
formatted to Short Time, can anyone help me? Many thanks.

 

[John Spencer (MVP)]

DateDiff calculates the number of boundaries crossed. That is 9:59 to 10:01 is
one hour (one boundary crossed). If you want to get fractional portions of an
hour you need to calculate the minutes and then divide by 60.

HoursBooked: DateDiff("n",[Start Time],[StopTime])/60

And "n" is the proper parameter for minutes, since "m" is already taken for months.

 

[John Vinson]

I am trying to calculate the length of time a room is booked for. I have
used the expression Hours Booked: DateDiff("h",[Start Time],[StopTime]) which
works for whole hours. The problem I have is it only seems to look at the
minutes if the start & stop minutes are the same i.e 09:15 to 12:15. If the
times are 09:30 to 12:00 it returns 3 hours not 2.5 hours. All times are
formatted to Short Time, can anyone help me? Many thanks.

The format is irrelevant; a date/time value is just a number, a count
of days and fractions of a day. If you want decimal hours, calculate
the datediff in miNutes (not Months) and divide by 60:

Hours Booked: Round(DateDiff("n", [Start Time], [StopTime]) / 60., 1)

John W. Vinson[MVP]

 

[John Vinson]

Now I just need to get the report to pick up the Hours Booked answer as its
not part of a table, so I might need to get back for help on this.

Either use a Query with this as a calculated field as the basis of
your report, or put the DateDiff()/60 expression in the Control Source
of a textbox on the report. If you're making the common assumption
that a report must be based on a table and must report only table
fields... it's a common assumption but it's a *wrong* assumption! Base
your reports on Queries, to look up fields from lookup tables, do
calculations, etc.

John W. Vinson[MVP]