calculate time in hours minutes and seconds between two dates

[The_Doc_Man]

CJ, I can duplicate your finding, which leaves me dumb-struck. I did this in a database three years ago and it worked as I said it did.

What is REALLY getting me confused is that when I try to look this up doing web searches for time formats, I'm even seeing two different results that appear to be contradictory.

One reference tells me that "nn" is two-digit minutes as part of a time formatting string, and that "mm" is two-digit months. Another reference tells me that "mm" is minutes and that "MM" is months, with no reference to "nn" at all. Yet both have URLs that make me think they are legit. This IS somewhat of a puzzler. It also appears to me that there are new options for the Access Format function relating to what you would see for a UTC formatted time.

I am wondering if enough changed for the format function that the "hhh" option is no longer valid. But there is no way for me to know that. I'll have to see if I can resurrect some of that old code. Might not be so easy because some of it belonged to the Dept. of Defense and I can't get to it now.

By the way, the effect of that extra "h" appears to be that it double-prints the number of hours. It is acting like the format is actually

format( 1.5, "h" ) & format( 1.5, "hh:nn:ss" )

which is how the 1212:00:00 string got built.

 

[Minty]

Bassed on a calandar thing I wrote ages ago nn is minutes because mm is months.

From Microsoft Access Web Page; https://support.office.com/en-gb/ar...ba24d881b698?ui=en-US&rs=en-GB&ad=GB&fromAR=1

Time format components

Format            Description

H               Hour in one or two digits, as needed (0 to 23).

hh              Hour in two digits (00 to 23).

N               Minute in one or two digits, as needed (0 to 59).

nn              Minute in two digits (00 to 59).

s               Second in one or two digits, as needed (0 to 59).

ss              Second in two digits (00 to 59).

 

[The_Doc_Man]

OK, guys. I'm going to "crawfish" on this one and say that I can't find where I did this so cannot confirm it. The apparently preferred method would therefore be to render by custom function.

If I can ever find the case where I did this, I'll come back to the post. In the meantime, since I can't corroborate it, I'll withdraw it as a claim.

 

[jai kushwaha]

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.

'---------------------------------------------------------------------------------------
' 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.

'---------------------------------------------------------------------------------------
' 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

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.

Can I use it for Excel Sheet also....


if yes than how....???

 

[jai kushwaha]

thanks i got it. it also working in ms excel. thanks a lot... to all of you...

 

[Grumm]

I once made a similar function too : http://www.access-programmers.co.uk/forums/showpost.php?p=1464028&postcount=41

You just need to remove the holiday part and it is fully functionnal with any output you want.

 

[jlcastrejon]

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

I was having the same problem that you, i decided to display the time elapsed between 2 dates and times in 2 different fields, in other words, one filed dispaly how many days and other how many hours and minutes was the elapsed time, as doc_man sugest i used CDate and as well CSng, doing that i was able to calculate the right time in accurate way, as you mention whe you don't do that and the time in hours is less than 24 hrs. the result is a difference between the highest number and the smallest without sign which is wrong.
If so far you havent solve your problem and for you would be useful a copy of my code i can post it for you, just let me know it.
Regards
Jose.

P.S.- There is a "small" problem...not with the code ....my comments on it are in spanish i hope that doesn't bother you

 

[Ihk]

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.

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.

Hi dear @The_Doc_Man ,
I was looking for difference in time format hh:mm:ss, your idea helped me and worked perfectly.
Now problem is there is no negative sign, all values are shown as positive. Time difference is correct but I want there should be negative sign if it is negative. Can you help me in this. thank you

 

[gemma-the-husky]

@Ihk

You wont get a negative time, and you won't get accurate results either. The time is part of a real number date which includes both a date element and a time element. By just using the time, you lose the date information that was part of the entire date/time

Sub times()
MsgBox Format(#8:00:00 AM# - #6:30:00 AM#, "hh:mm:ss") 'return  1:30
MsgBox Format(#6:00:00 AM# - #8:30:00 AM#, "hh:mm:ss") 'returns 2:30
End Sub

 

[Ihk]

@Ihk

You wont get a negative time, and you won't get accurate results either. The time is part of a real number date which includes both a date element and a time element. By just using the time, you lose the date information that was part of the entire date/time

Sub times()
MsgBox Format(#8:00:00 AM# - #6:30:00 AM#, "hh:mm:ss") 'return  1:30
MsgBox Format(#6:00:00 AM# - #8:30:00 AM#, "hh:mm:ss") 'returns 2:30
End Sub

Thank you for your response.
Actually, I have splitted date and time into two different fields. It is basically time attendance system. Now I want to calculate late comers. Many student / staff do come early. Who came earleir their time contains negative sign, those who came late has no sign (means positive) by my this follwing method of time difference.

TimeDIfference: (DateDiff("n",[EntryTime],[CheckInTime]))

This works perfectly fine, with negative and possitive values, results comes in minutes.
EntryTime = is actual set time by institution
CheckInTime = is the time when somebody checks in the system.
Negative minutes means for example -20, means this person came 20 in minutes earlier. While only 20 means 20 minutes later checked in.
This above is perfectly fine working. and on forms and reports I marked positive values in red.

Now question comes: I want a format to calculate in Hours:Minutes:Seconds
In above mentioned situation, if some one comes 20 seconds late, it will appear as 0 (zero), because this value is less than minute, result is zero minutes.
Hope I was able to explain, Now I tried

TimeDIfference: Format(CDate([EntryTime])-CDate(EntryTime),"hh:nn:ss")

This gives me time in format hour:Min:Sec, but no negative sign. While I want to mark positive values as on forms and reports.

 

[The_Doc_Man]

Splitting date and time in the table is the wrong idea. You can build a query that separately, as two fields IN THE QUERY, extracts formatted dates and formatted times from the SAME source of date/time differences. Here is your problem in a nutshell - time and date are TREATED as inseparable by the native Access date/time formatting routines. Therefore, if you want a computation of something time-related, you have to EITHER accept what Access will give you or you must "roll your own" formatting function.

IF you want to compute time differences in hours, minutes, and seconds in the traditional format of 12:34:56 then you must give the Format function the time difference as computed by taking the difference of two date fields and THEN you can use a format string of "hh:nn:ss" to structure the output of the function. If you have a difference in seconds (as for example, the result of a DateDiff in units of seconds), you must "massage" it.

If you have a LONG integer number of seconds and want the traditional time string, this will do the formatting.

HMS = Format( CDate(CDbl(Abs(Seconds))/86400.0), "hh:nn:ss: )
IF Seconds > 0 THEN
    HMS = "-" & HMS
END IF

Read this rather formidable expression from the inside out
strip out the sign (ABS);
convert integer seconds to a Double (CDbl);
divide by the number of seconds in a day (24*60*60=86400);
convert the result to a date (CDate);
pass it to the Format function (Format);
assign the string to a string variable for safe keeping (HMS=...);
check the original number of seconds for being negative (IF ...);
if it was negative, put a minus-sign in front of the formatted string (HMS = "-" & ...)

BUT if you just took the difference of two date/time variables and stored THAT as a date/time variable, as long as you took the difference in the right order, you can just directly format that result.

Now.... as to WHY you do this? Microsoft chose to use date/time formats as a "TIMELINE" variable - i.e. elapsed time in days and fractions of a day since a reference time, which makes negative numbers act like dates and times BEFORE the reference event. Fraction then represent fractions of a day. So a fraction of 0.5000..000 is noon, 0.000 is midnight.

As it happens (and I have tested this experimentally), a Date/Time variable will operate correctly between 1-Jan-0100 and 31-Dec-9999, which isn't a bad range. The reference date/time is midnight of 30-Dec-1899, which makes 31-Dec-1899 equal to day 1. (Go figure why they picked THAT date as the reference... I have no idea why they didn't make it 31-Dec-1999 as day 0.)

How to Work with Dates and Time in Microsoft Access

How to ensure dates and times are correctly recorded in Microsoft Access databases, and then used in queries, forms and reports

www.consultdmw.com

Since you wanted only convention date formats, you should be OK, but beware of time differences greater than 24 hours. In the past I was wrong about such formats because I ran across a way to do that abnormal formatting with a format template. It didn't work with Access. Turned out that error was from a non-Access environment. However, the Access time routines treat any time difference greater than 24 hours as 1 day and some number of hours. Therefore, sometimes the format routines get wonky when you give them something that is MORE than just a time of day.

 

[Jimmywilson1212]

Finding the time a vehicle spent parked, for instance the difference between "vehicle in time" and "vehicle out time" (both formatted as "DD-MM-YY HH:MM:SS"), involves a few steps. First, you'll need a way to convert these strings into a format suitable for calculations, like special date and time objects. Then, by subtracting these objects, you can determine the total duration the vehicle was parked. Finally, extract the individual hours, minutes, and seconds from this duration and format them in the desired "hh:mm:ss" way, potentially including leading zeros for a consistent two-digit display. This process allows you to easily calculate the exact parking time.

 

[Gasman]

Almost a 3 year old post?