Adding multiple start & finish times in Report

[Angelique]

The report that I am working with has multiple start & finish fields. Using =Format([StartTime1]-1-[FinishTime1],"Short Time&quot works for the first group but doesn't add up the next group e.g. StartTime2, FinishTime2 etc. to reach the desired answer.

Then if I put individual unbound textboxes for each Start & Finish Time (total of 4), it adds up ok. But I cannot get the totals from each 4 textboxes to calculate the total hours with:

Total Hours = Format([textbox1] + [textbox2] + [textbox3] + [textbox4], "Short Time&quot

to produce the correct answer, it gives me a 5 to 6 digit number instead.

I reckon I need to convert but I am not sure how!

Also if any of the fields (start or finish) are null (empty) then I don't get a result at all.

I just know someone out there can help! Working with dates & times has always been one of my pet hates.


Angelique

 

[RickSpr]

First let me mention that you shouldn't be using Format() here. Format() creates a text string from (in this case) a date/time value. Since you're doing arithmetic, the string then has to be converted back into a numeric value, so converting it to a string is a waste of time. Use Format() only when what you want to end up with is a string.

I'll assume the start and stop times are being keyed in hh:mm format, since you reference the "Short Time" format. The first thing to do is to convert them to date/time values, using the function CDate("#" & [StartTime1] & "#&quot. (Note: Since you aren't entering dates in the text box, the date part of the date/time value will be 0; it won't interfere with your calculations.) If you don't need the individual field values as dates, you can calculate the elapsed time with:
Dim Elapsed1 As Single
Elapsed1 = CDate("#" & [FinishTime1] & "#&quot - CDate("#" & [StartTime1] # "#&quot
Do the same for Elapsed2-4. The values of these variables will be floating point numbers containing the fraction of a day that each elapsed time is equivalent to.

(In your example, you reversed the start time and finish time, and subtracted 1 for some reason. I didn't understand that, so I'm using what makes sense to me.)

You can then calculate the total as:
Dim TotalHours As Single
TotalHours = (Elapsed1 + Elapsed2 + Elapsed3 + Elapsed4) * 24.0
Because the elapsed times are in units of days, we multiple by 24 hours/day to get elapsed hours.

Hope this works for you. Rick Sprague

 

[Angelique]

Rick,

Thanks, I'll try that, as I said dates & times aren't my thing.


Angelique