469,128 Members | 1,395 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

How to sum up the time more then 24 hours

1
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?
Aug 27 '16 #1
10 899
Narender Sagar
189 100+
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.
Aug 27 '16 #2
PhilOfWalton
1,430 Expert 1GB
Sorry, Don't think that will work for English dates (Assuming that the "8" in your example means August

In the debug window,
Expand|Select|Wrap|Line Numbers
  1. ?#14/08/2016 15:30# - #11/08/2016 10:00#
  2.  
gives -85.7708333333285 days which is not what is wanted (ignore the conversion to hours.

Using the American format
Expand|Select|Wrap|Line Numbers
  1. ?#08/14/2016 15:30# - #08/11/2016 10:00#
  2.  
gives the correct result of 3.22916666667152 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
Aug 28 '16 #3
Narender Sagar
189 100+
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
Aug 28 '16 #4
PhilOfWalton
1,430 Expert 1GB
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
Aug 28 '16 #5
jforbes
1,107 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function getHours(dDate1 As Date, dDate2 As Date) As Double
  2.     getHours = (dDate1 - dDate2) * 24
  3. End Function
If you then type in the following into the immediate window:
Expand|Select|Wrap|Line Numbers
  1. ?getHours(now(),  now()-.25)
  2.  6 
  3. ?getHours(now(),  now()-1)
  4.  24 
  5. ?getHours(now(),  now()-1.25)
  6.  30 
  7. ?getHours(now(),  now()-.1)
  8.  2.39999999996508 
  9. ?getHours(now(),  now()-.2)
  10.  4.79999999993015 
  11. ?getHours(now(),  now()-.3)
  12.  7.20000000006985 
  13. ?getHours(now(),  now()-.4)
  14.  9.60000000003492 
  15. ?getHours(now(),  now()-.5)
  16.  12 
Aug 28 '16 #6
PhilOfWalton
1,430 Expert 1GB
Hate to argue, but, using your function with real UK dates
Expand|Select|Wrap|Line Numbers
  1.  Debug.Print getHours(#02/02/2016#, #01/02/2016#)
  2.  744
  3.  
that is misinterpreted as 31 days between 2nd January & 2nd February

Phil
Aug 28 '16 #7
zmbd
5,400 Expert Mod 4TB
+ 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",#08/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.
Expand|Select|Wrap|Line Numbers
  1. Function CalcElapsedTimeAsHHMM(zInStartTime As Date, Optional zInEndTime As Date, _
  2.             Optional zInPostiveOnly As Boolean = True) As String
  3.     '
  4.     Dim zHoldHours As String
  5.     Dim zHoldMinuts As String
  6.     Dim zHoldElapsed As Long
  7.     Dim zStartTime As Date
  8.     Dim zEndTime As Date
  9.     '
  10.     zStartTime = zInStartTime
  11.     If (zInEndTime = 0) Then
  12.         zEndTime = Now()
  13.     Else
  14.         zEndTime = zInEndTime
  15.     End If
  16.     '
  17.     zHoldElapsed = DateDiff("n", zStartTime, zEndTime)
  18.     If zHoldElapsed < 0 And zInPostiveOnly Then zHoldElapsed = zHoldElapsed * -1
  19.     '
  20.     zHoldHours = zHoldElapsed \ 60
  21.     '
  22.     zHoldMinuts = zHoldElapsed Mod 60
  23.     '
  24.     CalcElapsedTimeAsHHMM = zHoldHours & ":" & zHoldMinuts
  25. End Function
Pasting this in to a standard module, <ctrl><g> to open the immediate pane and therein type
?CalcElapsedTimeAsHHMM(#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.
Aug 28 '16 #8
PhilOfWalton
1,430 Expert 1GB
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
Aug 29 '16 #9
zmbd
5,400 Expert Mod 4TB
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...)
Aug 29 '16 #10
PhilOfWalton
1,430 Expert 1GB
OK, I give in.

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

Phil
Aug 29 '16 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Paul Wagstaff | last post: by
18 posts views Thread by Dirk Hagemann | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.