calculate time in hours minutes and seconds between two dates (1 Viewer)

jai kushwaha

Registered User.
Local time
Tomorrow, 02:58
Joined
Nov 1, 2015
Messages
61
Hello friends I have a field name vehicle in time which has data type like 12-11-16 11:15:12 and another field vehicle out time which is also have same data type 13-11-16 23:11:15 now I want to calculate the time between them in hh: mm: ss formate? What to do
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:28
Joined
Feb 19, 2013
Messages
16,553
I agree with Bob - note you have to use maths, you cannot format it (max hh is 23, max nn is 59)
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
I agree with Bob and CJ. Use the datediff function to get the difference in seconds, then use math/arithmetic to break the Total seconds into hours, minutes and seconds.

Good luck.
 

jai kushwaha

Registered User.
Local time
Tomorrow, 02:58
Joined
Nov 1, 2015
Messages
61
Is there any way to change a positive no into date formate without maths.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
Suggest you give us an example.

Data showing what you have, and also the desired result.
 

jai kushwaha

Registered User.
Local time
Tomorrow, 02:58
Joined
Nov 1, 2015
Messages
61
I have two fields vehicle in timeT0 and vehicle out time T1 and both fields have the same date and time formate T0=12-11-16 1:00:00 and T1=13-11-16 15:15:00 and calculated them with datediff function in forms of second and the result is 137700 but I want a result formate like 38:25:00. Now how to get this desired formate with accurate result.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
Well, there are
- 60 sec in 1 minute
-60 minutes in 1 hour

So in x seconds there are x/60 minutes
or x/60/60 hours.

137700 sec = 13700/60/60 =38.25 hours.

but .25 hours (1/4 hour is 15 minutes)
so you have 38 hrs 15 min 0 sec
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:28
Joined
Feb 28, 2001
Messages
27,001
Jai: Actually, if all you need is the hours, minutes, and seconds for display purposes (not for further math) then try this.

Assume you have OutTime and InTime, both in the same date format (whatever it is). Further assume you want the formatted string in variable HMSDifference.

Code:
HMSDifference = Format(  ( InTime - OutTime ), "hhh:mm:ss" )

If InTime and OutTime are in string variables rather than date variables, you need to use CDate(InTime) as opposed to "raw" InTime, and ditto for OutTime. The expression I used requires the times to be actual Date variable format, so CDate would be used if you really had strings.

CJ, normally I would not disagree with you , but the "hhh:mm:ss" formatting string gives you elapsed hours, minutes, seconds and CAN exceed 24 hours.
 

jai kushwaha

Registered User.
Local time
Tomorrow, 02:58
Joined
Nov 1, 2015
Messages
61
Well thanks my friend but i am not getting anywhere as i am confused how to use cdate and how? Do you have your WhatsApp no or email it will be easier to send you photographs so that you can understand what i want?? But yes you got what result i want and what to do but somehow I am not getting as you told. Can you please give me your contact.. .....thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:28
Joined
Feb 28, 2001
Messages
27,001
jai, I don't normally do private exchanges. That doesn't benefit the community.

The only way that formula wouldn't work is if there is a problem with the dates or if HMSDiff isn't a string (to receive the string generated by Format() function. If the Out date (check-out date/time) is earlier than the In date (check-in date/time) and if both variables are of type Date, that formula should work to give you a string representation of hours, minutes, seconds. If the dates you have are actually in string variables, then that formula would be

Code:
HMSDiff = Format( ( CDate( InDate ) - CDate( OutDate ) ), "hhh:mm:ss" )

As long as the vehicle was checked out first and checked in later, this is the formula to use to get that string as described. Note that if the "raw" values are in an INTEGER format (e.g. WORD or LONG) you will NOT get the correct results. The dates must either be date/time STRING variables (in which case you convert them using CDate(x) or they must be Access DATE variables in which case CDate(x) is not required.
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
A test ---Access wants Date format as M/D/Y---
Code:
Sub dateFmt()
'from
'http://www.access-programmers.co.uk/forums/showthread.php?p=1515802
    Dim d1 As Date
    Dim d2 As Date
    Dim x As Single, y As Single
    d1 = #11/12/2016 1:00:00 AM#
    d2 = #11/13/2016 11:11:15 PM#
    Debug.Print "A", Format((d1 - d2), "hhh:mm:ss") ' from post 9
    Debug.Print "B", DateDiff("s", d1, d2)

    'Get Difference in seconds
    x = DateDiff("s", d1, d2)
    Debug.Print "1 seconds", x
    
    'Get difference in Minutes
    x = x / 60
    Debug.Print "2 minutes", x
    
    'Get difference in Hours
    x = x / 60
    Debug.Print "3 hours", x

    'Calculate fractional  hours into hours and minutes
    y = (x - CInt(x)) * 60
    Debug.Print "4 hrs:min", CInt(x) & " hrs And " & y & " Minutes"

    'Calculate Hours and fractional minutes into Hours, minutes and seconds

    Debug.Print "5 hrs:min:sec", CInt(x) & ":" & CInt(y) & ":" & (y - CInt(y)) * 60
End Sub

Results of the Debug.Print statements

Code:
A             2222:11:15
B              166275 
1 seconds      166275 
2 minutes      2771.25 
3 hours        46.1875 
4 hrs:min     46 hrs And 11.25 Minutes
5 hrs:min:sec 46:11:15

These results indicate that the 137700 seconds you gave us in post 7 is incorrect.
 
Last edited:

JHB

Have been here a while
Local time
Today, 22:28
Joined
Jun 17, 2012
Messages
7,732
..These results indicate that the 137700 seconds you gave us in post 7 is incorrect.
You've the wrong time in d2, correct is #11/13/2016 3:15:00 PM# and not #11/13/2016 11:11:15 PM#
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
Thanks JHB. I think my numbers/dates got mixed up when I entered them as D/M/Y then realized they should be m/d/y.
At one point it showed year as 2013???

Sorry for any inconvenience to others:

Here is revised code and results

Code:
'---------------------------------------------------------------------------------------
' Procedure : dateFmt
' Author    : mellon
' Date      : 26-Dec-2016
' Purpose   :To get difference of 2 date/times to hh:mm:ss
      'http://www.access-programmers.co.uk/forums/showthread.php?p=1515802
'---------------------------------------------------------------------------------------
'
Sub dateFmt()
      
          Dim d1 As Date
          Dim d2 As Date
          Dim x As Single, y As Single
10       On Error GoTo dateFmt_Error

20        d1 = #11/12/2016 1:00:00 AM#
'
' d2 corrected as per JHB comments.
30        d2 = #11/13/2016 3:15:00 PM#
40        Debug.Print "A", Format((d1 - d2), "hhh:mm:ss")
50        Debug.Print "B", DateDiff("s", d1, d2)

          'Get Difference in seconds
60        x = DateDiff("s", d1, d2)
70        Debug.Print "1 seconds", x
          
          'Get difference in Minutes
80        x = x / 60
90        Debug.Print "2 minutes", x
          
          'Get difference in Hours
100       x = x / 60
110       Debug.Print "3 hours", x

          'Calculate fractional  hours into hours and minutes
120       y = (x - CInt(x)) * 60
130       Debug.Print "4 hrs:min", CInt(x) & " hrs And " & y & " Minutes"

          'Calculate Hours and fractional minutes into Hours, minutes and seconds

140       Debug.Print "5 hrs:min:sec", CInt(x) & ":" & CInt(y) & ":" & (y - CInt(y)) _
              * 60

150      On Error GoTo 0
160      Exit Sub

dateFmt_Error:

170       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & _
              ") in procedure dateFmt of Module AWF_Related"
End Sub

Revised result:
Code:
A             1414:15:00
B              137700 
1 seconds      137700 
2 minutes      2295 
3 hours        38.25 
4 hrs:min     38 hrs And 15 Minutes
5 hrs:min:sec 38:15:0
 

jai kushwaha

Registered User.
Local time
Tomorrow, 02:58
Joined
Nov 1, 2015
Messages
61
Thanks JHB. I think my numbers/dates got mixed up when I entered them as D/M/Y then realized they should be m/d/y.
At one point it showed year as 2013???

Sorry for any inconvenience to others:

Here is revised code and results

Code:
'---------------------------------------------------------------------------------------
' Procedure : dateFmt
' Author    : mellon
' Date      : 26-Dec-2016
' Purpose   :To get difference of 2 date/times to hh:mm:ss
      'http://www.access-programmers.co.uk/forums/showthread.php?p=1515802
'---------------------------------------------------------------------------------------
'
Sub dateFmt()
      
          Dim d1 As Date
          Dim d2 As Date
          Dim x As Single, y As Single
10       On Error GoTo dateFmt_Error

20        d1 = #11/12/2016 1:00:00 AM#
'
' d2 corrected as per JHB comments.
30        d2 = #11/13/2016 3:15:00 PM#
40        Debug.Print "A", Format((d1 - d2), "hhh:mm:ss")
50        Debug.Print "B", DateDiff("s", d1, d2)

          'Get Difference in seconds
60        x = DateDiff("s", d1, d2)
70        Debug.Print "1 seconds", x
          
          'Get difference in Minutes
80        x = x / 60
90        Debug.Print "2 minutes", x
          
          'Get difference in Hours
100       x = x / 60
110       Debug.Print "3 hours", x

          'Calculate fractional  hours into hours and minutes
120       y = (x - CInt(x)) * 60
130       Debug.Print "4 hrs:min", CInt(x) & " hrs And " & y & " Minutes"

          'Calculate Hours and fractional minutes into Hours, minutes and seconds

140       Debug.Print "5 hrs:min:sec", CInt(x) & ":" & CInt(y) & ":" & (y - CInt(y)) _
              * 60

150      On Error GoTo 0
160      Exit Sub

dateFmt_Error:

170       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & _
              ") in procedure dateFmt of Module AWF_Related"
End Sub

Revised result:
Code:
A             1414:15:00
B              137700 
1 seconds      137700 
2 minutes      2295 
3 hours        38.25 
4 hrs:min     38 hrs And 15 Minutes
5 hrs:min:sec 38:15:0







thanks my dear friend but how to use this code to calculate time difference b/w two column in query. where to use it i have a column name TimeIn and other is TimeOut and both have time in as same format as you described above now i made a query in which i build a exp:[TimeOut]-[TimeIN] but getting An error plzz help.:banghead:
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
jai,

Here is a function based on my previous post. I left the Debug.Print statements in the code as comments just in case you want to see the interim values.
Code:
'---------------------------------------------------------------------------------------
' Procedure : fnDateFmt
' Author    : mellon
' Date      : 05-Jan-2017
' Purpose   :A Function to get difference of 2 date/times to hh:mm:ss
      'http://www.access-programmers.co.uk/forums/showthread.php?p=1515802
'
' This function takes 2 parameters. Both are type Date.
' d1 is earliest date, d2 is latest date
' output is a string showing hh:min:sec between d1 and d2.
'---------------------------------------------------------------------------------------
'
Function fnDateFmt(d1 As Date, d2 As Date) As String
    
          Dim x As Single, y As Single

10       On Error GoTo fnDateFmt_Error

          'Get Difference in seconds
20        x = DateDiff("s", d1, d2)
          'Debug.Print "1 seconds", x
          
          'Get difference in Minutes
30        x = x / 60
          'Debug.Print "2 minutes", x
          
          'Get difference in Hours
40        x = x / 60
          'Debug.Print "3 hours", x

          'Calculate fractional  hours into hours and minutes
50        y = (x - CInt(x)) * 60
          'Debug.Print "4 hrs:min", CInt(x) & " hrs And " & y & " Minutes"

          'Calculate Hours and fractional minutes into Hours, minutes and seconds

60        fnDateFmt = CInt(x) & ":" & CInt(y) & ":" & (y - CInt(y)) * 60

70       On Error GoTo 0
80       Exit Function

fnDateFmt_Error:

90        MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure fnDateFmt of Module AWF_Related"

End Function

Here is a test routine using your dates.

Code:
'---------------------------------------------------------------------------------------
' Procedure : testfnDateFMT
' Author    : mellon
' Date      : 05-Jan-2017
' Purpose   :This is a test routine to test the function fnDateFMT.
'---------------------------------------------------------------------------------------
'
Sub testfnDateFMT()
10        Dim date1 As Date: date1 = #11/12/2016 1:00:00 AM#
20        Dim date2 As Date: date2 = #11/13/2016 3:15:00 PM#
30       On Error GoTo testfnDateFMT_Error

40        Debug.Print " Formatted difference[hr:min:sec]" & vbCrLf _
                      & " between " & date1 & "  and " & date2 & " is: " & fnDateFmt(date1, date2)

50       On Error GoTo 0
60       Exit Sub

testfnDateFMT_Error:

70        MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure testfnDateFMT of Module AWF_Related"
End Sub

Here are the results

Code:
Formatted difference[hr:min:sec]
 between 12-Nov-2016 1:00:00 AM  and 13-Nov-2016 3:15:00 PM is: 38:15:0

Based on your latest post, your would use code similar to this in your query

TimeDifference:fnDateFMT(TimeIn,TimeOut)

after you save the function fnDateFMT in a standard module.

Good luck.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:28
Joined
Feb 19, 2013
Messages
16,553
@Doc

Just noticed your comment

CJ, normally I would not disagree with you , but the "hhh:mm:ss" formatting string gives you elapsed hours, minutes, seconds and CAN exceed 24 hours
perhaps I'm using it wrong

if I try a difference of 12 hours (which is 0.5)

format(0.5,"hhh:nn:ss")

I get 1212:00:00

or 36 hours - format(1.5,"hhh:nn:ss")

again 1212:00:00
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
Doc, CJ,

I noticed the same thing. In my post #14 the result A shows what was returned using Doc's format statement.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:28
Joined
Feb 19, 2013
Messages
16,553
Oh Yeah! teach me to skim through
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:28
Joined
Jan 23, 2006
Messages
15,364
Well, I didn't highlight it --thought I may have misunderstood something and just continued with my sample code.

Skim through - I understand --sometimes takes me 8 posts to get the drift of some posts????
 

Users who are viewing this thread

Top Bottom