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
+ 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
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" 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" 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" wrote in message news:Xn**********************@129.250.170.99... "Allen Browne" 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" 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.