Time Calculations

C

Craig G.

I am trying to calculate "Start time" + "End Time" on a form in ACCESS 2007

example: I started @ 3:00 am on tuesday and did not finishish until 5:00 pm
on saturday.
How do I make a calculaution to give me a total for this?

what fields should be included in the datasheet and the form?
 
T

Tom Wickerath

Hi Craig,

Here are some articles that you may find helpful:

On time and how much has elapsed
http://office.microsoft.com/en-us/access/HA011102181033.aspx

Using dates and times in Access
http://office.microsoft.com/en-us/access/HA010546621033.aspx

ACC2000: How to Calculate Daily Hours Based on Clock In/Clock Out Times
http://support.microsoft.com/?id=237958

Note: Disregard the "ACC2000:" in the title of the third article. It should
apply equally well to other versions of Access.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
P

pauld

Tom,

I need to calculate elapsed time in hours:minutes:seconds to score a race
that usually exceeds 23:59:59.

Is there a way to calculate this in Access ? It's simple in Excel by setting
the time format to 37:50:55 and all values over 23:59:59 calculate properly.

I know nothing about Visual Basic and hope there is a way to handle this
with using my knowledge of Access. I want to use Access to do this since the
other important information about the race is in Access. We've figured out
how to do everything else, but this one baffles us.
 
D

Douglas J. Steele

The generally agreed correct solution is to store the duration as number of
seconds, and write your own functions to convert from seconds to hh:mm:ss
and vice versa.

I did write about a kludge in my October, 2003 "Access Answers" column in
Pinnacle Publication's "Smart Access". You can download the column (and
sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html
 
J

James A. Fortune

Chris2 said:
I am trying to calculate "Start time" + "End Time" on a form in ACCESS 2007

example: I started @ 3:00 am on tuesday and did not finishish until 5:00 pm
on saturday.
How do I make a calculaution to give me a total for this?

what fields should be included in the datasheet and the form?
Click to expand...


Craig G.,

You cannot perform a "Start Time" + "End Time" operation, not if "Start Time" and "End
Time" are Datetime values.

You can subtract them, "End Time" - "Start Time".

In VBA, you would do this:

Public Function ElapsedTimeFromDatetimes(StartDatetime As Date _
, EndDatetime As Date) As String

Dim TotalSeconds As Integer
Dim ElapsedHours As Integer
Dim ElapsedMinutes As Integer
Dim ElapsedSeconds As Integer

TotalSeconds = DateDiff("s", StartDatetime, EndDatetime)

ElapsedHours = TotalSeconds \ 3600
ElapsedMinutes = (TotalSeconds \ 60) Mod 60
ElapsedSeconds = TotalSeconds Mod 60

ElapsedTime = Format(CStr(ElapsedHours), "00") & ":" & _
Format(CStr(ElapsedMinutes), "00") & ":" & _
Format(CStr(ElapsedSeconds), "00")

ElapsedTimeFromDatetimes = ElapsedTime

End Function

The function call would appear as:

ElapsedTimeFromDatetimes([StartDatetime], [EndDatetime])


In a single expression, you would use:

Format(DateDiff("s", [StartDatetime], [EndDatetime]) \ 3600, "00") & ":" &
Format(DateDiff("s", [StartDatetime], [EndDatetime]) \ 60 MOD 60, "00") & ":" &
Format(DateDiff("s", StartDatetime], [EndDatetime]) MOD 60, "00")


Sincerely,

Chris O.
Click to expand...

I do something similar here:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/be7e101c5176ebd8

James A. Fortune
[email protected]
 
M

Michael Gramelspacher

Tom,

I need to calculate elapsed time in hours:minutes:seconds to score a race
that usually exceeds 23:59:59.

Is there a way to calculate this in Access ? It's simple in Excel by setting
the time format to 37:50:55 and all values over 23:59:59 calculate properly.

I know nothing about Visual Basic and hope there is a way to handle this
with using my knowledge of Access. I want to use Access to do this since the
other important information about the race is in Access. We've figured out
how to do everything else, but this one baffles us.
Click to expand...

in a query:
Right("0"+CStr(DateDiff("s",a.start_date,NZ(a.finish_date,a.start_date))
\3600),2)+":"
+Right("0" & CStr(DateDiff("s",a.start_date,NZ(a.finish_date,a.start_date)) Mod
3600)\60,2)+":"
+Right("0"+CStr((DateDiff("s",a.start_date,NZ(a.finish_date,a.start_date)) Mod
3600) Mod 60),2) AS [Elapsed Time]
 
Top