Adding Hours minutes and seconds

Question

Dear Developers,

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

<tfoot></tfoot>

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

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.

Regards.

M. Khurram Siddiqui

Answer 1

Your data is stored in what data types?

Answer 2

i is stored in date/time type in access database

Answer 3

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)

Answer 4

The 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")
    Else
        TimeElapsed = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If

End Function

e.g with your value for Program:

? TimeElapsed(5.49301388887397)
131:49:56

or by passing True into the function as the optional blnShowdays argument:

? TimeElapsed(5.49301388887397, True)
5:11:49:56

---

Ken Sheridan, Stafford, England

Answer 5

Well thanks for your reply bt my issue is not microseconds all i want is to add hours

Answer 6

dear Ken

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

Answer 7

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

HoursDuration:TimeElapsed([Duration])

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:

=TimeElapsed([Duration])

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