Calculating both Rounded Time and rounded Elapsed Time in a Query

[caddcop]

I have data with a start and stop time field. I have to treat any portion of each clock hour as 1 hour. So if the start time is 9:55 AM and the end time is 10:05 AM, I have to count the 5 minutes before 10 as one hour and the five minutes after ten as 1 hour for a total of 2 hours. I used the Hour function on each to round the time to a clock hour, adding 1/24 to the end time to get the next highers whole hour. Unfortunately, that makes the numbers useless for elapsed time calculation if the span a crossing from AM to PM.
Is there a way to round these times and correctly calculate the elapsed time with my partial clock hour rule, all within a query?

[June7]

Are these date/time type fields? Will any period cross midnight?

Use military (24-hr) clock time instead of AM/PM. Hour() will return the 24-hour clock value, except for midnight, that will be 0 for either clock.

Try:

Start: Hour([StartTime]) + IIf(Hour([StartTime])<Hour([EndTime]),0,1)

End: Hour([EndTime]) + 1

[caddcop]

There is always the possibility of usage times crossing over at midnight, but it should occur infrequently. Since my target is to count highest concurrent usage per day, the usages that might occur crossing midnight should never involve the calculations for peak usage. That will almost always occur during normal business hours. In fact, usually, when that occurs, it means someone left a program running overnight and that really should not be happening. Any such anomalies in the data might help flag those situations and help us to discourage it from occurring.

[June7]

Does my suggestion work?
Crossing midnight really complicates. I'll have to leave that to you to figure out. Maybe you need another calc to test if start date and end date are the same.

[caddcop]

I have been able to use 24 hr time.

I also might not really need the elapsed time value after all.

Is there a way to display times in my query as 12 hour time, even though the data is using 24 hour times?

[June7]

Use Format function on a full date/time value. Access Help has guidelines or Web search.

If the data is in date/time type field then it is not in any particular time structure. Date/time data is really stored as a double number. Defining the field as date/time tells Access to display the number in a date/time form. As far as I know, the default display is AM/PM.