Date & Time difference & calculations (1 Viewer)

I need to calculate (in a query) the Actual Time of a job. I have the start date, start time, end date & end time (all separate fields). This seems to works ok by subtracting the start from the end.
Then I need to calculate the Estimated Time (time it should have taken). This is done by taking the Quantiy divided by the Rate-Per-Hour. This seems to work out ok in the Query, e.g., 101000 / 15000 = 6.66667 hours. But when I display this result in a report as a "Short Time", I get 16:00 instead of 6:40. How can I get the correct display?
The other problem is that I need to divide the Estimated Time by the Actual Time. This also gives me wierd results - I suppose because one is a decimal format time and the other is a "Short Time" format. How can I get the correct answer?
Many thanks,
Michael

Maybe something here will shed some light: http://www.mvps.org/access/datetime/index.html

Having separate fields for Date and Time is a bad idea. Keep in mind the Date datatype is actually a Double number, specifically formatted for Date and Time functions. The number represents the number of days since Saturday, December 30, 1899, while the fraction part of the number represents the amount of time elapsed since midnight. While your calculation gives you 6.66667 hours, you are trying to apply the date conversion function to show you the time in formatted function. Before I show you the recommended fix, let me show you what is actually happening.

First, open up the Debug or Immediate window. Duplicate what you have tried the first time, as follows:

?Format(6.66667, "Short Time")

Indeed, you will get 16:00. As you may or may not have figured out, 16:00 (or 4:00 PM) is exactly 2/3 of the way through the day, which corresponds to the .66667 fraction part of the number.

Now, if you try to format it using the date value, such as:

?Format(6.66667, "Short Date")

you would get Jan 5 1900, which is exactly 6 days after the originating date of December 30, 1899.

Following this pattern, then, doing the following:

?Format(6.66667, "mmm dd yyyy h:nn:ss")

the answer is Jan 05 1900 16:00:00.

So now you know how date formatting works, you see why it is not a good idea to have dates and times for the same event in different fields.

NOW, for getting the answer you want, which is a properly formatted time string that shows the hours and minutes based on the calculation.

Since there are 24 hours in a day, you need to convert the 6.66667 number to a fraction of a day. Therefore,

6.6667/24 = 0.277779166666667

Now, since the number is less than 1, you can use the short time format for displaying the time elapsed. Here is the continuation:

?format(0.277779166666667 , "h:nn")

In fact, the whole calculation can be done in one line as follows:

?format((101000 / 15000)/24), "h:nn")

comes up with 6:44, which is exactly what you are looking for. (In your original calculation, (101000 / 15000) actually equals 6.73..., NOT 6.66667, but I used your original calculation for the first few steps).

Now this is not the best way to perform your solution, because when you calculate a number greater than one, you have the same problem you had originally. In that case, you can try to use functions that parse the string the way you want at the link that Rural Guy gave.