473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Time/ Date How much time has elapsed

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 3009
ADezii
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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
3,080 Expert 2GB
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
need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked.

thank,
chris
Aug 18 '07 #5
ADezii
8,834 Expert 8TB
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
3,532 Expert 2GB
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
8,834 Expert 8TB
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
8,834 Expert 8TB
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
3,532 Expert 2GB
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

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

Similar topics

1
by: Sara Khalatbari | last post by:
Is there a Modules in Python that returns the time & date of today when ran?
18
by: Robin Lawrie | last post by:
Hi again, another problem! I've moved from an Access database to SQL server and am now having trouble inserting dates and times into seperate fields. I'm using ASP and the code below to get the...
8
by: peterbe | last post by:
What's the difference between time.clock() and time.time() (and please don't say clock() is the CPU clock and time() is the actual time because that doesn't help me at all :) I'm trying to...
9
by: Bob Achgill | last post by:
I would like to use the timestamp on files to manage the currency of support files for my VB windows application. In this case I would only put the timestamp of the file in the management database...
13
by: maflatoun | last post by:
Hi, I have the following function to convert UTC time to Local time. It works perfect for GMT- (Minus) time zones however it provides incorrect results for GMT+(Plus) time zones? // Format to...
15
by: Cerebral Believer | last post by:
Hi all, I am a newbie to JavaScript and am just trying to get a script going that will write the ful date and time to each webpage as it is viewed. Can anyone point out what mistakes there are...
2
by: drurjen | last post by:
Good morning. I am importing an XLS file into one of my tables. The fields are: Date Id Time IO 12/22/2006 2 12:48:45 PM 9 12/22/2006 16 5:40:55 AM 1 12/22/2006 16 12:03:59 PM 2 ...
2
by: silverburgh.meryl | last post by:
Hi, I have the following code which spawn a number of thread and do something (in the run method of MyThread). how can I record how much time does EACH thread takes to complete the 'run'? ...
2
by: Yew12 | last post by:
We are trying to get the following script to display the full date and time. The field we are calling does have both date and time in. Unfortunatly its only returning the date. So I tried putting...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.