Calculate the total amount of elapsed time in hours

[487Jack]

I am trying to calculate the total amount of time in hours that it takes to complete a test. I had two fields in my table called:

StartTime
EndTime

I than used the following formula to calculate the total time:

=Format([StartTime] -1 -[EndTime]’ “ShortTime”)

This formula works find. But some of the test that I run can run for days or even weeks. I then added two additional fields to my table to capture the start date of the test and end date of the test:

StartDate
EndDate

Is there a way to calculate the total test time in hours using the StartDate and StartTime and EndDate and EndTime?

Your help would be greatly appreciated.

Thank you,
Stephen Jones

 

[missinglinq]

StartDT = Me.StartDate & " " & Me.StartTime

EndDT = Me.EndDate & " " & Me.EndTime

Me.TotalTestTime = DateDiff("n", StartDT, EndDT) / 60

will give you the total hours in hours and fraction of an hour. Depending on your particular needs, you may want to round TotalTestTime.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[487Jack]

Thank you very much for your response. I should have mention that I am beginner with access and I that have never written code before. If you could tell me were to enter the code I should be able to figure it out from there.

Thank you,
Stephen Jones

 

[SkipVought]

"I had two fields in my table..."

Date/Time values are NUMBERS.

Me.TotalTestTime = ([EndDate]+[EndTime])-([StartDate]+[StartTime]) / 60
[code]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[SkipVought]

oops...

"I had two fields in my table..."

Date/Time values are NUMBERS.

Me.TotalTestTime = ([EndDate]+[EndTime])-([StartDate]+[StartTime]) * 24

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[aperez54]

You probably could've just kept the 2 fields with a format of general date to capture both date and time.

I believe you are trying to achieve something like this:

start end total
10/10/08 10:00:00 AM 10/11/08 10:00:00 AM 24.00
10/11/08 10:00:00 AM 10/11/08 9:00:00 PM 11.00

To do so, in you query, use formula:

([end]-[start])*24

HTH