By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 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
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,597
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

NeoPa
Expert Mod 15k+
P: 31,186
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

nico5038
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

ADezii
Expert 5K+
P: 8,597
need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked.

thank,
chris
Expand|Select|Wrap|Line Numbers
  1. Dim dteBeginTime As Date, dteEndTime As Date, intHoursDiff
  2.  
  3. dteBeginTime = #5:30:00 PM#
  4. dteEndTime = #3:30:00 AM#
  5.  
  6. intHoursDiff = DateDiff("h", dteBeginTime, dteEndTime)
  7.  
  8. If intHoursDiff < 0 Then
  9.   Debug.Print "Elapsed Hours: " & 24 - Abs(intHoursDiff)
  10. Else
  11.   Debug.Print "Elapsed Hours: " & intHoursDiff
  12. End If
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Elapsed Hours: 10
Aug 18 '07 #6

missinglinq
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
  1. Private Sub Finish_BeforeUpdate(Cancel As Integer)
  2.  TotalSeconds = DateDiff("s", start, finish)
  3.  
  4.  HoursLapsed = Int(TotalSeconds / 3600)
  5.  If HoursLapsed = 0 Then HoursLapsed = "00"
  6.  
  7.  SecondsLeft = TotalSeconds Mod 3600
  8.  
  9.  MinutesLapsed = Int(SecondsLeft / 60)
  10.  If MinutesLapsed = 0 Then MinutesLapsed = "00"
  11.  If MinutesLapsed <> 0 And Len(MinutesLapsed) = 1 Then MinutesLapsed = "0" & MinutesLapsed
  12.  
  13.  SecondsLapsed = SecondsLeft Mod 60
  14.  
  15.  If SecondsLapsed = 0 Then SecondsLapsed = "00"
  16.  If SecondsLapsed <> 0 And Len(SecondsLapsed) = 1 Then SecondsLapsed = "0" & SecondsLapsed
  17.  
  18.  TotalTime = HoursLapsed & ":" & MinutesLapsed & ":" & SecondsLapsed
  19.  
  20.  End Sub
Welcome to TheScripts, Jackson1 and cedonahue3!

Linq ;0)>
Aug 18 '07 #7

ADezii
Expert 5K+
P: 8,597
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
  1. Private Sub Finish_BeforeUpdate(Cancel As Integer)
  2.  TotalSeconds = DateDiff("s", start, finish)
  3.  
  4.  HoursLapsed = Int(TotalSeconds / 3600)
  5.  If HoursLapsed = 0 Then HoursLapsed = "00"
  6.  
  7.  SecondsLeft = TotalSeconds Mod 3600
  8.  
  9.  MinutesLapsed = Int(SecondsLeft / 60)
  10.  If MinutesLapsed = 0 Then MinutesLapsed = "00"
  11.  If MinutesLapsed <> 0 And Len(MinutesLapsed) = 1 Then MinutesLapsed = "0" & MinutesLapsed
  12.  
  13.  SecondsLapsed = SecondsLeft Mod 60
  14.  
  15.  If SecondsLapsed = 0 Then SecondsLapsed = "00"
  16.  If SecondsLapsed <> 0 And Len(SecondsLapsed) = 1 Then SecondsLapsed = "0" & SecondsLapsed
  17.  
  18.  TotalTime = HoursLapsed & ":" & MinutesLapsed & ":" & SecondsLapsed
  19.  
  20.  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

ADezii
Expert 5K+
P: 8,597
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
  1. If Not IsDate(Me![Start]) Or Not IsDate(Me![Finish]) Then Exit Sub
Aug 18 '07 #9

missinglinq
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

Post your reply

Sign in to post your reply or Sign up for a free account.