By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,939 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,939 IT Pros & Developers. It's quick & easy.

Hours available in a day

P: n/a
Hi,

I have a table called Bookings which has two important columns;
Booking_Start_Time and Booking_End_Time. These columns are both of type
DATETIME. Given any day how can I calculate how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many
hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day), can this be done with a query
or do I have to work it out in my code?

Thanks for your help

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Create a query into this table, and enter this into a fresh column (Field
row):
Minutes: DateDiff("n", [Booking_Start_Time], [Booking_End_Time])

This field gives you the number of minutes in each booking.
You can sum the result for the number of minutes booked.
The number of minutes unbooked is then:
DateDiff("n", #9:00#, #17:30#) - [Minutes]

To display the result as hours and minutes:
=([Minutes] \ 60) & Format([Minutes] Mod 60, "\:00")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Shaun" <sh***@mania.plus.com> wrote in message
news:bl**********@reader-00.news.insnet.cw.net...
I have a table called Bookings which has two important columns;
Booking_Start_Time and Booking_End_Time. These columns are both of type
DATETIME. Given any day how can I calculate how many hours are available
between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time
of any bookings on that day), can this be done with a query
or do I have to work it out in my code?

Nov 12 '05 #2

P: n/a
"Allen Browne" <ab***************@bigpond.net.au> wrote in news:5adfb.134762$bo1.25822@news-
server.bigpond.net.au:
Create a query into this table, and enter this into a fresh column (Field
row):
Minutes: DateDiff("n", [Booking_Start_Time], [Booking_End_Time])

This field gives you the number of minutes in each booking.
You can sum the result for the number of minutes booked.
The number of minutes unbooked is then:
DateDiff("n", #9:00#, #17:30#) - [Minutes]

To display the result as hours and minutes:
=([Minutes] \ 60) & Format([Minutes] Mod 60, "\:00")


Not sure why you would need to use DateDiff when (Booking_End_Time - Booking_StartTime)*1440 would
do. Calling DateDiff on each row could be very time consuming.
--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos
Nov 12 '05 #3

P: n/a
Performing direct math on date/time fields instead of using the functions is
not really a good idea.

You might be disappointed if you expected this to return 2 hours:
? (#1/2/1890 1:00# - #1/1/1890 23:00#) * 24

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Ross Presser" <rp******@NOSPAM.imtek.com.invalid> wrote in message
news:Xn**********************@129.250.170.99...
"Allen Browne" <ab***************@bigpond.net.au> wrote in news:5adfb.134762$bo1.25822@news- server.bigpond.net.au:
Create a query into this table, and enter this into a fresh column (Field row):
Minutes: DateDiff("n", [Booking_Start_Time], [Booking_End_Time])

This field gives you the number of minutes in each booking.
You can sum the result for the number of minutes booked.
The number of minutes unbooked is then:
DateDiff("n", #9:00#, #17:30#) - [Minutes]

To display the result as hours and minutes:
=([Minutes] \ 60) & Format([Minutes] Mod 60, "\:00")

Not sure why you would need to use DateDiff when (Booking_End_Time -

Booking_StartTime)*1440 would do. Calling DateDiff on each row could be very time consuming.
--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and you can travel to places you never heard of and still understand some

people." -- Alex K. Angelopoulos
Nov 12 '05 #4

P: n/a
"Allen Browne" <ab***************@bigpond.net.au> wrote in
news:II*********************@news-server.bigpond.net.au:
Performing direct math on date/time fields instead of using the
functions is not really a good idea.

You might be disappointed if you expected this to return 2 hours:
? (#1/2/1890 1:00# - #1/1/1890 23:00#) * 24


That is very interesting, not what I would have expected, not something that would happen in any other
language I have ever seen in my life, and very Microsoft-ish. Not only do they want everyone to use
Microsoft software, they punish anyone who has ever used non-Microsoft software by writing the internal
routines to catch you up in contraintuitive ways.

I found a more complete discussion at
http://groups.google.com/gr*********....microsoft.com
if anyone else is interested. The upshot is that if you never work with dates prior to 12/31/1899, this
behavior will never occur; and that using direct math on datetime fields can introduce some rounding
errors. E.g., even if you use "modern" dates, you won't get an integer number of hours:

? (#1/2/1990 1:00# - #1/1/1990 23:00#) * 24
1.99999999988358


--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.