473,390 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

How to sum up the time more then 24 hours

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 1190
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
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#
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#
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.

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

Aug 28 '16 #5
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
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
that is misinterpreted as 31 days between 2nd January & 2nd February

Aug 28 '16 #7
5,501 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
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

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

Aug 29 '16 #9
5,501 Expert Mod 4TB
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
1,430 Expert 1GB
OK, I give in.

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

Aug 29 '16 #11

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

Similar topics

by: Mike A | last post by:
Hi, I'm hoping someone can help me with this. I have a URL for which I'd like to limit access to by time. For example,say I have a URL that I don't want accessable on Monday mornings between...
by: Bill | last post by:
In ASP, how can I quickly return the server time + 2 hours, using VBScript? With TIME(), I've found functions for date arithmetic, but I can't find anything for time arithmetic. Thanks!!!
by: Paul Wagstaff | last post by:
Hi All I have an aggregate query that groups by each user, then calculates Sum(TimeInvested). I need to display the total time (per user) in hrs / mins e.g. 45:30 (= 45 hrs 30 mins). Can...
by: patsandy | last post by:
Hi, I am Patricia and I would like to get some assistance to write a Program in C. Your help would be greatly appreciated. The Program must calculate how long it takes to fly from one place...
by: =?Utf-8?B?SmVubnlZ?= | last post by:
i have installed AS4.5 and it syncs with the pda phone one time. then the next time i plug in the usb, it says no connection and AS does not automatically start even though it is checked. I try...
by: Dirk Hagemann | last post by:
Hello, From a zone-file of a Microsoft Active Directory integrated DNS server I get the date/time of the dynamic update entries in a format, which is as far as I know the hours since january 1st...
by: miraheem | last post by:
hi, i have 3 text boxes the first one is names as: txt_tof the second one: txt_duration the third one: txt_ETA now i need to add the time entered in the text box "txt_tof" with the value...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.