Adding up Durations(1.15 as 1h15m)

[hobman]

I have been asking this question couple of times but I don't think I am making myself clear. Here it goes again. I am tracking meetings that happend. With other fields, the meeting also has a "Duration "field. in that field how long the meeting lasted is entered. for example if it took 1hour and 15 minutes the user enteres 1.15.

Later, I have a report that displays meeting for selected dates. like the following

1/1/03 1.15
2/2/04 1.30
2/2/02 2.45

I want to add all the duration together for example if we add the above durations we get 5.30 or 5hr30min.
The way I have it not, it sums it up as regular numbers so it sums it up as 4.9

any suggestion would be appreciated.

 

[kerplunkitt]

Hi there
i have done exactly this for a friend and it works fine..
i created 4 text boxes - one called minutesworked and one called hoursworked, which show the hours and minutes, the other two are calles timestart and timeend - into which the user enters the start and finish times.
Make sure the table has the timestart and timeend Date/time data types selected for the fields or it won't work properly.
the other two data types can be set to numbers.

Enter this code on a command button:

totalminutes = DateDiff("n", [timestart], [timeend])
hoursworked = totalminutes \ 60
minutesworked = totalminutes Mod 60

Hope this helps - if you need a bit more help then let me know...
Regards
'viper

 

[MichaelRed]

To accomplish this 'properly', you need to understand the data type / format of (Date)/Time in Ms. A.

It is really "just" a double, where the whole number part represents the number of days since Dec. 30, 1899 and the decimal (fraction) represents the proportion of any remaining 'day'. In this context, 1 second represents 1/84000 of a day.

These can be illustrated:

? Format (1/84000, "h:n:s")
0:0:1:


? Format(0, "Short Date")
12/30/1899

I'm not trying to demean your experience and/or knowlege, but the storage of the durations in such a format is somewhat to the detriment of their manipulation. Ms. A. has the date type which in directly intended to include this information. See the ubiquitous {F1} (aka HELP) with KeyWord "Date DataType" for some explination. Follow the "See Also" links for even more information.

A simple change to the formulation and storage of your information would greatly simplify the process. In the meantime (since I don't actually expect you to go forth and study this) the below module will reasonably work to do the calculation based on the arcane and weird selection of formatting the input information. Using your sample data, the call would be:

basDecHM2Time(1.15, 1.45, 2.30)

Public Function basDecHM2Time(ParamArray HM_Time() As Variant) As Variant

    Dim Idx As Integer
    Dim totTime As Date
    Dim Hrs As Integer
    Dim Mins As Integer

    While Idx <= UBound(HM_Time)

        Hrs = Int(HM_Time(Idx))
        Mins = (HM_Time(Idx) - Hrs) * 100
        totTime = DateAdd("n", Mins, totTime)
        totTime = DateAdd("h", Hrs, totTime)
        Idx = Idx + 1

    Wend

    basDecHM2Time = Format(totTime, "h:n")

End Function

Without more detailed knowledge of your intended source and destination of the elements, I cannot further comment on the specifics of using this procedure or any derivatives thereof, but can suggest that you need some remedial instruction on the craft.


MichaelRed