435,264 Members | 1,056 Online
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

# Time/ Date How much time has elapsed

 P: 1 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 Feb 10 '07 #1
9 Replies

 Expert 5K+ P: 8,638 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. Feb 10 '07 #2

 Expert Mod 15k+ P: 31,494 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."""). Feb 11 '07 #3

 Expert 2.5K+ P: 3,072 Checkout the datetime differences and other items at: http://www.mvps.org/access/datetime/index.html Fine site with much info ! Nic;o) Feb 11 '07 #4

 P: 1 need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked. thank, chris Aug 18 '07 #5

 Expert 5K+ P: 8,638 need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked. thank, chris Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers Elapsed Hours: 10 Aug 18 '07 #6

 Expert 2.5K+ P: 3,532 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 Expand|Select|Wrap|Line Numbers 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)> Aug 18 '07 #7

 Expert 5K+ P: 8,638 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 Expand|Select|Wrap|Line Numbers 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. Aug 18 '07 #8

 Expert 5K+ P: 8,638 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: Expand|Select|Wrap|Line Numbers If Not IsDate(Me![Start]) Or Not IsDate(Me![Finish]) Then Exit Sub Aug 18 '07 #9

 Expert 2.5K+ P: 3,532 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)> Aug 18 '07 #10