Need help in calculating the difference between date and time

[BearBug]

Hi all,
would appreciate if you guys could help me out.
I'm new to MS Access 2002 and currently had a project going on.

Currently, I have 2 fields. "DateTime_Out" and "DateTime_In".

I would like to calculate the days and time difference in the 2 fields as in
[DateTime_IN]-[DateTime_Out] giving me to results in No. of Days / No. of Hours.
However the results return is always not what I want.

Can anyone guide me how to do so?

 

[RV]

BearBug,

welcome to the forum.
The forum comes with a search facility.
Use this facility and you'll find out that similar questions have been raised quite often, including solutions.

RV

 

[EMP]

Assuming [DateTime_IN] is the start time and [DateTime_Out] the end time, the integer of ([DateTime_Out] - [DateTime_IN]) gives you the number of days.

([DateTime_Out] - [DateTime_IN])*24 gives you the total number of hours with several decimal places.


So you can manipulate these two expressions to arrive at whatever you need.

 

[raskew]

Along the same lines--but possibly a more generic solution for dealing with time elements--the following two functions, used in conjunction, will return a days:hours:minutes:seconds string which you can manipulate as necessary.

Function MyDates(pStartDte As Date, pEndDte As Date) As String
'*************************************************************
'Purpose:   Convert a date difference into a
'           days:hours:minutes:seconds string
'Coded by:  raskew
'Calls:     Function dhms()
'Inputs:    From the debug window:
'         ? MyDates(#3/31/05 10:44:55 PM#, #4/2/05 9:07:03 PM#)
'Output:    01:22:22:08
'*************************************************************

Dim dblHold As Double

    'find number of seconds between the dates
    dblHold = Int((pEndDte - pStartDte) * 86400)
    'call dhms() to return a days:hours:minutes:seconds string
    MyDates = dhms(dblHold)
    
End Function

Public Function dhms(ByVal pSec As Single) As String
'*********************************************
'Purpose:   Convert number of seconds into a
'           days:hours:minutes:seconds string
'Coded by:  raskew
'Inputs:    ? dhms(96121)
'Output:    01:02:42:01
'*********************************************

Dim n       As Integer
Dim sinHold As Single
Dim strFmt  As String
Dim strHold As String

    sinHold = pSec
    strFmt = "00"
    strHold = ""
    
    For n = 1 To 3
       strHold = strHold & Format(Int(sinHold / Choose(n, 86400, 3600, 60)), strFmt) & ":"
       sinHold = sinHold Mod (Choose(n, 86400, 3600, 60))
    Next n
    dhms = strHold & Format(sinHold, strFmt)

End Function

HTH - Bob

 

[EMP]

Bob,
It seems your functions sometimes return 1 second short.


I tried this query:-
SELECT ID, DateTime_IN, DateTime_Out,
MyDates([DateTime_In],[DateTime_Out]) AS [Bob's function],
Format(Int([DateTime_Out]-[DateTime_IN]),"00") & ":" & Format([DateTime_Out]-[DateTime_IN],"hh:nn:ss") AS [My expression]
FROM tblData;

And these were the results it returned:-

ID  DateTime_IN		    DateTime_Out	   Bob's function  My expression
1   1/12/2005 11:15:00 AM   1/12/2005 2:00:00 PM   00:02:45:00	    00:02:45:00
2   1/12/2005 11:15:00 AM   1/13/2005 2:00:00 PM   01:02:45:00	    01:02:45:00
3   2/28/2005 11:15:00 AM   3/2/2005 11:00:15 AM   [b][color=red]01:23:45:14[/color][/b]	    01:23:45:15
4   2/28/2005 11:15:00 AM   3/2/2005  2:00:15 PM   [b][color=red]02:02:45:14[/color][/b]	    02:02:45:15
5   2/28/2005 11:15:00 AM   3/2/2005  2:45:00 PM   02:03:30:00	    02:03:30:00

 

[raskew]

Hi -

Good catch!

My code (erroneously, it appears) returned just the integer portion, e.g.

pStartDte = #2/28/2005 11:15:00 AM#
pEndDte = #3/2/2005 11:00:15 AM#
? dblHold = Int((pEndDte - pStartDte) * 86400)
171914

Had I returned the entire number:
? dblHold = (pEndDte - pStartDte) * 86400
...the result would be:
171914.999999944

...enough to cause a 1 second error when fed to
Function dhms().

Thanks for that.

Bob

 

[mkelly]

shorttime: Int([job completed time]-[job start time]-CDate("0:0:" & DatePart("s",[job completed time]-[job start time])))*24+DatePart("h",[job completed time]-[job start time]-CDate("0:0:" & DatePart("s",[job completed time]-[job start time]))) & Format([job completed time]-[job start time]-CDate("0:0:" & DatePart("s",[job completed time]-[job start time])),":nn")

 

[EMP]

shorttime: Int([job completed time]-[job start time]-CDate("0:0:" & DatePart("s",[job completed time]-[job start time])))*24+DatePart("h",[job completed time]-[job start time]-CDate("0:0:" & DatePart("s",[job completed time]-[job start time]))) & Format([job completed time]-[job start time]-CDate("0:0:" & DatePart("s",[job completed time]-[job start time])),":nn")

This simple expression can give you the same result:-
Hours Minutes: Int([job completed time]-[job start time])*24+DatePart("h",[job completed time]-[job start time]) & Format([job completed time]-[job start time],":nn")


Edited to correct an error.

 

[BearBug]

Assuming [DateTime_IN] is the start time and [DateTime_Out] the end time, the integer of ([DateTime_Out] - [DateTime_IN]) gives you the number of days.

([DateTime_Out] - [DateTime_IN])*24 gives you the total number of hours with several decimal places.


So you can manipulate these two expressions to arrive at whatever you need.

Hi pal, I tried using the above said method to calculate the time difference, however I encounter a problem.

The following are the values in the fields.
TimeIn = 2330hrs
TimeOut = 0130hrs

If we were to count the number of hours logically, we will get back 2hrs difference. But if I were to use the method that you mentioned, I will get back -22hrs.

Is there anyway I can solve this problem?

 

[BearBug]

This simple expression can give you the same result:-

Hours Minutes: Int(([job completed time]-[job start time])*24) & Format([job completed time]-[job start time],":nn")

,

Hey pal, had tried your queries code, however encounter a problem here.

Assuming the following values are in the fields.
[job completed time] = 03/05/2005 01:30 AM
[job start time] = 03/04/2005 09:30 PM

Time difference between these 2 date/time should be 4hrs.

But with your code, it gave me the result of 3hrs.
Is it possible for you to assist me further in solving the problem?

 

[BearBug]

Bob,
It seems your functions sometimes return 1 second short.


I tried this query:-
SELECT ID, DateTime_IN, DateTime_Out,
MyDates([DateTime_In],[DateTime_Out]) AS [Bob's function],
Format(Int([DateTime_Out]-[DateTime_IN]),"00") & ":" & Format([DateTime_Out]-[DateTime_IN],"hh:nn:ss") AS [My expression]
FROM tblData;

And these were the results it returned:-

ID  DateTime_IN		    DateTime_Out	   Bob's function  My expression
1   1/12/2005 11:15:00 AM   1/12/2005 2:00:00 PM   00:02:45:00	    00:02:45:00
2   1/12/2005 11:15:00 AM   1/13/2005 2:00:00 PM   01:02:45:00	    01:02:45:00
3   2/28/2005 11:15:00 AM   3/2/2005 11:00:15 AM   [b][color=red]01:23:45:14[/color][/b]	    01:23:45:15
4   2/28/2005 11:15:00 AM   3/2/2005  2:00:15 PM   [b][color=red]02:02:45:14[/color][/b]	    02:02:45:15
5   2/28/2005 11:15:00 AM   3/2/2005  2:45:00 PM   02:03:30:00	    02:03:30:00

Hey pal! After trying so many methods to calculate the date/time difference, should say a bit thank you to you!

Had achieve the results required. but is it possible for the results return format to be should in hours and mins alone? had tried several ways to change the 00:03:00 format to 03 hrs 00 mins. But unsuccessful.

Sorry to bother you so much... really very new to microsoft access and seriously need a lot of help from you pple..

 

[EMP]

I have added a new query in the database with this expression (it should correct the previous error of showing 4 hours as 3 hours):

Int([DateTime_Out]-[DateTime_In])*24+DatePart("h",[DateTime_Out]-[DateTime_In]) & " hrs " & Format([DateTime_Out]-[DateTime_In],"nn") & " mins" AS [Hrs Mins]

When the query is run, the expression should return:
2 hrs 45 mins
26 hrs 45 mins
47 hrs 45 mins
50 hrs 45 mins
51 hrs 30 mins
4 hrs 00 mins


As the number of hours between two date time fields may exceed 2 digits, hrs is shown as 2, not 02.

 

[BearBug]

brother! thanks! finally got it right! thanks a lot!