Sunday, June 10, 2012 9:30 AM
I need some help in time calculation, Actually i have different entries with with the time value I converted the format to hh:nn:ss but this format did not work for me as i calculating more that 100 hours by adding. when i coped the data in excel and convert the format to [h]:mm:ss i got the desired results but so far i am fail to do it in access. I am pasting both the results of access and excel, Kindly help.
Access Query Result
rundownQ Crit1 Duration Branded Anouncement 1.21745370197459E-02 Branded Promo 0.39423611111124 Coming Up Next 6.57083334226627E-03 Commercial 1.30441203696682 Cross Promotion 4.0824074021657E-03 LIVE/PCR/FEED/VTR 1.12431944448326 Program 5.49301388887397 Promos 1.2062240740197 Songs 0.135567129589617 Tomorrow Promos 0.321541666700796 Tonight Promos 0.659710185296717 Transitions
Converting format didnt work in access
Now Copy this data to Excel and change format to [h]:mm:ss
Branded Anouncement 0:17:31.880 Branded Promo 9:27:42.000 Coming Up Next 0:09:27.720 Commercial 31:18:21.200 Cross Promotion 0:05:52.720 LIVE/PCR/FEED/VTR 26:59:01.200 Program 131:49:56.400 Promos 28:56:57.760 Songs 3:15:13.000 Tomorrow Promos 7:43:01.200 Tonight Promos 15:49:58.960 Transitions 0:45:58.360
this is what i want in access.
M. Khurram Siddiqui
Sunday, June 10, 2012 10:35 AMYour data is stored in what data types?
Sunday, June 10, 2012 11:04 AMi is stored in date/time type in access database
Sunday, June 10, 2012 11:30 AM
Unfortunately, you cannot get microseconds from Access's Date/Time data type: the best you'll be able to do with 1.21745370197459E-02 is 00:17:32.
That being said, recognize that the date/time data type is an 8 byte floating point number. The integer portion represents the date as the number of days relative to 30 Dec, 1899, and the decimal portion represents the time as a fraction of a day. You can simply add together the dates, and them format them at the end. The problem is that if the total exceeds 24 hours, the formatted total will simply drop the days part. For one way to get around this, see http://www.vb123.com/200310_ds_time_2.htm
Note that how you format a field does not change its value. You can format the field displaying the time as hh:nn:ss and still be able to add the values together.
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
Sunday, June 10, 2012 1:26 PMThe following function will return the value in hours:minutes:seconds format or, optionally in days hours:minutes:seconds
Public Function TimeElapsed(dtmTime As Date, _
Optional blnShowdays As Boolean = False) As String
' Returns a date/time value as a duration
' in format hh:nn:ss, or d:hh:nn:ss if optional
' blnShowDays argument is True.
Dim lngDays As Long
Dim strDays As String
Dim strHours As String
' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")
If blnShowdays Then
TimeElapsed = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
TimeElapsed = Format((Val(strDays) * 24) + Val(strHours), "00") & _
e.g with your value for Program:
or by passing True into the function as the optional blnShowdays argument:
? TimeElapsed(5.49301388887397, True)
Ken Sheridan, Stafford, England
- Marked As Answer by Yoyo JiangMicrosoft Contingent Staff, Moderator Friday, June 22, 2012 6:22 AM
Sunday, June 10, 2012 1:29 PMWell thanks for your reply bt my issue is not microseconds all i want is to add hours
Thursday, June 28, 2012 1:59 PM
I tried this function in both report n query but got the errors can you please help me using the syntax, i may be using the function wrong.....thanks
Thursday, June 28, 2012 5:58 PMFirstly you need to paste the function into a standard module, and , if it's a new module, save it under a name other than that of the function , e.g. mdlDateTimeStuff.
In a query you call it by passing the name of the date/time column into it as its argument. So in the 'field' row of a blank column in query design view you'd put something like this:
You can of course name the column whatever you wish rather than HoursDuration In a computed control in a form the expression as the ControlSource property would be:
One thing to note is that, as the value returned by the function is a string expression, you cannot do any subsequent date/time arithmetic on it. If you needed to do this the arithmetic should be done on the original Duration value and the result returned in the hours:minutes:seconds format by means of the function.
Ken Sheridan, Stafford, England
- Marked As Answer by Yoyo JiangMicrosoft Contingent Staff, Moderator Thursday, July 05, 2012 3:49 AM