Time/ Date How much time has elapsed

[Jackson1]

I am new to access and have created a database. I have created a couple of fields
Date/ Time of Call
Date and Time of placement
Date and time of procedure

I need to calculate the differance between Date and time of initial call
Differance between Date / Time of placement
Differance between Date/ Time of procedure.

Times are in 2:30:00 PM and 14: 30 formats.

Can I perform these calculations and how do I go about doing this. I am at loss.
Thank you
J

[ADezii]

I am new to access and have created a database. I have created a couple of fields
Date/ Time of Call
Date and Time of placement
Date and time of procedure

I need to calculate the differance between Date and time of initial call
Differance between Date / Time of placement
Differance between Date/ Time of procedure.

Times are in 2:30:00 PM and 14: 30 formats.

Can I perform these calculations and how do I go about doing this. I am at loss.
Thank you
J

For what Interval do you wish to calculate the differences? For detailed information on calculating the differences between Dates and Times, please reference the DateDiff() Function. If you encounter any problems, please let us know.

[NeoPa]

A simple subtraction will give you the result you're after.
Unfortunately, the result will be a simple floating point result showing number (including fraction) of days. This can be formatted to show (separately) the number of days ("0") and Hours minutes and seconds ("HH:nn:ss" or "h ""hours, ""n ""minutes and ""s""seconds."" ").

[nico5038]

Checkout the datetime differences and other items at:
http://www.mvps.org/access/datetime/index.html

Fine site with much info !

Nic;o)

[cedonahue3]

need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked.

thank,
chris

[ADezii]

need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked.

thank,
chris

Dim dteBeginTime As Date, dteEndTime As Date, intHoursDiff
 
dteBeginTime = #5:30:00 PM#
dteEndTime = #3:30:00 AM#
 
intHoursDiff = DateDiff("h", dteBeginTime, dteEndTime)
 
If intHoursDiff < 0 Then
  Debug.Print "Elapsed Hours: " & 24 - Abs(intHoursDiff)
Else
  Debug.Print "Elapsed Hours: " & intHoursDiff
End If

OUTPUT:

Elapsed Hours: 10

[missinglinq]

With the controls named Start, Finish and TotalTime holding the obvious data, this will do it down to the second! If you want to drop the seconds, omit Lines 13-16 and change Line 18 to read

TotalTime = HoursLapsed & ":" & MinutesLapsed

Private Sub Finish_BeforeUpdate(Cancel As Integer)
 TotalSeconds = DateDiff("s", start, finish)
 
 HoursLapsed = Int(TotalSeconds / 3600)
 If HoursLapsed = 0 Then HoursLapsed = "00"
 
 SecondsLeft = TotalSeconds Mod 3600
 
 MinutesLapsed = Int(SecondsLeft / 60)
 If MinutesLapsed = 0 Then MinutesLapsed = "00"
 If MinutesLapsed <> 0 And Len(MinutesLapsed) = 1 Then MinutesLapsed = "0" & MinutesLapsed
 
 SecondsLapsed = SecondsLeft Mod 60
 
 If SecondsLapsed = 0 Then SecondsLapsed = "00"
 If SecondsLapsed <> 0 And Len(SecondsLapsed) = 1 Then SecondsLapsed = "0" & SecondsLapsed
 
 TotalTime = HoursLapsed & ":" & MinutesLapsed & ":" & SecondsLapsed
 
 End Sub

Welcome to TheScripts, Jackson1 and cedonahue3!

Linq ;0)>

[ADezii]

With the controls named Start, Finish and TotalTime holding the obvious data, this will do it down to the second! If you want to drop the seconds, omit Lines 13-16 and change Line 18 to read

TotalTime = HoursLapsed & ":" & MinutesLapsed

Private Sub Finish_BeforeUpdate(Cancel As Integer)
 TotalSeconds = DateDiff("s", start, finish)
 
 HoursLapsed = Int(TotalSeconds / 3600)
 If HoursLapsed = 0 Then HoursLapsed = "00"
 
 SecondsLeft = TotalSeconds Mod 3600
 
 MinutesLapsed = Int(SecondsLeft / 60)
 If MinutesLapsed = 0 Then MinutesLapsed = "00"
 If MinutesLapsed <> 0 And Len(MinutesLapsed) = 1 Then MinutesLapsed = "0" & MinutesLapsed
 
 SecondsLapsed = SecondsLeft Mod 60
 
 If SecondsLapsed = 0 Then SecondsLapsed = "00"
 If SecondsLapsed <> 0 And Len(SecondsLapsed) = 1 Then SecondsLapsed = "0" & SecondsLapsed
 
 TotalTime = HoursLapsed & ":" & MinutesLapsed & ":" & SecondsLapsed
 
 End Sub

Welcome to TheScripts, Jackson1 and cedonahue3!

Linq ;0)>

Nice approach, ling. Just a suggestion, you may want to allow for the post Midnight barrier: a Start Time of 17:30 and a Finish Time of 03:30 will yield -14 instead of 10.

[ADezii]

need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked.

thank,
chris

One point to note:
However you arrive at the final calculations, you should always make sure you have valid entries in the Start and Finish Times as in:

If Not IsDate(Me![Start]) Or Not IsDate(Me![Finish]) Then Exit Sub

[missinglinq]

Yeah, I know! The full version does! It was actually designed to use a TimeIn and TimeOut button that used Now() which assures real dates/times, but the supervisors needed to go in and change some times, and I knew they'd mess it up! If the code's to be used in a situation where time only is input manually, then you would have to add code to check that valid times have been entered and to handle situations where midnight occurs between Start and Finish.

Linq ;0)>