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

#### jai kushwaha

##### Registered User.
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
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
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.
Is there any way to change a positive no into date formate without maths.

#### jdraw

##### Super Moderator
Staff member
Suggest you give us an example.

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

#### jai kushwaha

##### Registered User.
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
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
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.
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
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
A test ---Access wants Date format as M/D/Y---
Code:
``````Sub dateFmt()
'from
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
..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
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
'---------------------------------------------------------------------------------------
'
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.
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
'---------------------------------------------------------------------------------------
'
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
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
'
' 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``````

TimeDifference:fnDateFMT(TimeIn,TimeOut)

after you save the function fnDateFMT in a standard module.

Good luck.

#### CJ_London

##### Super Moderator
Staff member
@Doc

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
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
Oh Yeah! teach me to skim through

#### jdraw

##### Super Moderator
Staff member
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????

Replies
9
Views
412
Replies
8
Views
628
Replies
4
Views
158
Replies
2
Views
258
Replies
27
Views
705