How to sum up the time more then 24 hours

[CM LOW]

I'm summing up time spent on jobs over a week, some of these jobs last
greater than 24 hours and when access sums these up it appears that it
starts again at 00:00 once it gets past 23:59.

Is it possible to display the time taken in hours and mins past the 24 hour
clock? i.e 34:23?

[Narender Sagar]

Lets assume you have two fields : JobStart & JobEnd
You need to set data type as Date/Time
Now you have to maintain the time taken by a given job as follows :
e.g. JobStart : 11/08/2016 10:00 and JobEnd : 14/08/2016 15:30
Now create a query and create a field name Diff: [JobEnd]-[JobStart]
Now create another field TimeTaken : [Diff]*24 (simply multiple the Diff. with 24.)
You'll get the numbers your want.

[PhilOfWalton]

Sorry, Don't think that will work for English dates (Assuming that the "8" in your example means August

In the debug window,

?#14/08/2016 15:30# - #11/08/2016 10:00#

gives -85.770833333328 5 days which is not what is wanted (ignore the conversion to hours.

Using the American format

?#08/14/2016 15:30# - #08/11/2016 10:00#

gives the correct result of 3.2291666666715 2 days

I have a mass of code, which AFIK has a function similar to the DateDiff function that should deal with dates in any format because the date is entered as a string.

Phil

[Narender Sagar]

Phil, I'm still wondering if this is the case!
Although I'm still not so convinced...! According to my knowledge, the dates are nothing but the number of days starting from 01.01.1900 (which is day 1 as per computer). So logically the difference between the two dates should give same number (whether it is UK or American date system).. But on the other hand, If you are saying so, it is a learning for me.
Thanks & regards

[PhilOfWalton]

Trouble is that in UK 11/08/2016 means 11th August 2016, whilst the Americans regard it as 8th November 2016.
I believe that 14/08/2016 will be interpreted correctly by the Americans as there is no 14th month

Phil

[jforbes]

Narender Sagar has the solution I would recommend. Subtracting the Dates from one another and then multiplying by 24 will return the amount of hours between the dates.

Try out this function:

Public Function getHours(dDate1 As Date, dDate2 As Date) As Double
    getHours = (dDate1 - dDate2) * 24
End Function

If you then type in the following into the immediate window:

?getHours(now(),  now()-.25)
 6 
?getHours(now(),  now()-1)
 24 
?getHours(now(),  now()-1.25)
 30 
?getHours(now(),  now()-.1)
 2.39999999996508 
?getHours(now(),  now()-.2)
 4.79999999993015 
?getHours(now(),  now()-.3)
 7.20000000006985 
?getHours(now(),  now()-.4)
 9.60000000003492 
?getHours(now(),  now()-.5)
 12

[PhilOfWalton]

Hate to argue, but, using your function with real UK dates

 Debug.Print getHours(#02/02/2016#, #01/02/2016#)
 744

that is misinterpreted as 31 days between 2nd January & 2nd February

Phil

[zmbd]

+ Allen Browne addresses international dates in Access here:
International Dates in Access To summarize Mr. Browne's article; all Access internal functions, as well as those handled in via JET/ACE data-engine, attempt to use the MM/DD/YYYY format internally. There are exceptions to this rule; however, IMHO, never rely upon these exceptions!

+ This is especially true when you enclose the date in the hashtag/pound symbol! Access will interpret the date as #MM/DD/YYYY hh:nn:ss# ignoring your regional settings. For example #31/01/2016#, Access going to try and interpret this as "31-Jan-2016" no matter where in the world you live, disregarding your regional settings.

That is to say, any date enclosed in the "#" is a "Real USA" date and will attempt to interpret any numbers in the first two positions that are greater than 12 as days within the implied month in the second two digits. This interpretation is obviously not full proof, and once again, IMHO, never rely upon these exceptions!

+ Next instead of using straight subtraction I would use the DateDiff() function using the dates in the #MM/DD/YYY hh:nn:ss# format returning the number of minutes between the dates:
Using Sagar's date and time in post#2, properly formatted using the US/English style, we have:
datediff("n",#0 8/11/2016 10:00#,#08/14/2016 15:30#)
Returning 4650 minutes

Now OP wanted this returned as HH:MM so we need:

Integer divide by 60 for the hours
4650\60
returning the integer part 77

for the minutes
4650 MOD 60
returning 30

Combine these as a string value to return the desired format.

Function CalcElapsedTimeAsHHMM(zInStartTime As Date, Optional zInEndTime As Date, _
            Optional zInPostiveOnly As Boolean = True) As String
    '
    Dim zHoldHours As String
    Dim zHoldMinuts As String
    Dim zHoldElapsed As Long
    Dim zStartTime As Date
    Dim zEndTime As Date
    '
    zStartTime = zInStartTime
    If (zInEndTime = 0) Then
        zEndTime = Now()
    Else
        zEndTime = zInEndTime
    End If
    '
    zHoldElapsed = DateDiff("n", zStartTime, zEndTime)
    If zHoldElapsed < 0 And zInPostiveOnly Then zHoldElapsed = zHoldElapsed * -1
    '
    zHoldHours = zHoldElapsed \ 60
    '
    zHoldMinuts = zHoldElapsed Mod 60
    '
    CalcElapsedTimeAsHHMM = zHoldHours & ":" & zHoldMinuts
End Function

Pasting this in to a standard module, <ctrl><g> to open the immediate pane and therein type
?CalcElapsedTim eAsHHMM(#08/11/2016 10:00#,#08/14/2016 15:30#)
The returned string will be "77:30"
Keeping in mind, one cannot use this string in subsequent numeric/date-time calculations.

Mr. Browne also has a version located here:
Calculating elapsed time


CM LOW
Please keep in mind here, we normally ask that you submit any work you've already tried. It helps us to know where you are at knowledge wise, keeps us from re-hashing something you've already tried, and it shows that you have a vested interest in doing the work.

[PhilOfWalton]

Sorry to keep coming back, but the problem is that in the US, the date format is MM/DD/YYYY and in the UK the date format is DD/MM/YYYY.

DateDiff works with the American format, and, depending on the dates in question will sometimes work with the UK format Depending on whether formatting the UK date as an American date gives a valid date.

For example a UK date of 06/03/2016 (6th March) will be interpreted by the DateDiff function as 3rd June.
On the other hand, 16/03/2016 (16th March) will be interpeted correctly as there is no 16th month.

Now I know that jforbes & zmbd are both American, so they may never have come across this proble.

We don't know where CM Low comes from, so we don't know his date format.

AS I said, I have a lot of code which I will post if needed, which I believe will deal with most date formats (even Afrikaans which I believe use YYYY/MM/DD)

Phil

[zmbd]

Phil,
I'm not sure why we are belaboring this point.
It doesn't matter where CM LOW is located, CM LOW is still going to have to deal with this quirk of Access when using VBA and most of the Macro-Scripting functions.

+ Access has used the MM/DD/YYYY internally for Macro-Scripting and all VBA since it was introduced (IIRC 1993 - with Office4.2 (??) with Access-1.1, I very briefly used this version and then we moved to Office95 and then quickly to Office97), and quite often Jet (now ACE) flavor of SQL

+ Mr. Browne's article explains this very well which is why I placed the link.

+Microsoft's own website:

ACC: How Windows Regional Settings Affect Microsoft Access (article)
(...)
When you use a regional setting other than English (United States), consider the following:
•In Visual Basic for Applications, you must use U.S./English Date formats when you create SQL statements.
•In the query design grid, you can use International Date formats.
(...)

One will note: Not only must one use the US format for SQL statements in VBA, it is also best practice to use the US format for all Date usage within VBA code, especially with the "#" literals.
As for using the international date format in the query editor, I would still use the #DD/MM/YYYY hh:nn:ss# format whenever possible; however, that interface seems to be a bit more robust when it comes to dates.

BTW: My company is international, I occasionally pull data from ORACLE and MYSQL servers, and many of my instruments export their data in the "YYYY-MM-DD hh:nn:ss" or other more non-standard formats; thus, I often deal with date/time issues. I really love it when ORACLE exports insist on tacking the time region on the end of the date (CST, GMT, MTN, UTC-4, UTC+6, etc... etc...)

[PhilOfWalton]

OK, I give in.

Have had problems in the past with VBA dates, but all solved.

Phil