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
9 3009
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 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.""").
need to calculate hours worked. Example, 17:30 to 3:30 should be 10 hours worked.
thank,
chris
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:
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)>
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.
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
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)> Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sara Khalatbari |
last post by:
Is there a Modules in Python that returns the time &
date of today when ran?
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
...
|
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'?
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |