Solved Sum of time for time above 24 hours (1 Viewer)

[E9-Tech]

I have the query

SELECT
    tblDuties.PayPeriod,
    Sum(tblDuties.Overtime) AS SumOfOvertime
FROM
    tblDuties
GROUP BY
    tblDuties.PayPeriod
ORDER BY
    tblDuties.PayPeriod;

is showing the result in decimal


I need the total in hh:mm keeping in mind that the sum could exceed the 24 hours (eg hh:mm 32:17), my overtime field in the table is set to Format: Short Time and the Input Mask: 00:00;0;_

How do I achieve this?

 

[CJ_London]

The format hh:mm can only show a maximum of 23;59. One more minute and you are into another day 1-00:00

Time is store as the fractional part of a Decimal - midday=0.5

So you will need a function to convert the day part (the value before the decimal point)

See your other thread below

 

[DHookom]

Doug Steele has a more complete date time formatting function here.

 

[GustavBrock]

You'll need a small helper function for that:

' Format the count of days, hours, and minutes of Date1 as
' hours and minutes.
' By default, the local time separator is used.
' Optionally, specify a custom time separator.
'
' Example:
'   Date1:      #10:03# + #20:01#
'   returns:    30:04
'
' 2022-06-07. Cactus Data ApS, CPH.
'
Public Function FormatHourMinute( _
    ByVal Date1 As Date, _
    Optional ByVal Separator As String) _
    As String

    Dim TextHour        As String
    Dim TextMinute      As String
    Dim TextHourMinute  As String
    
    TextHour = CStr(Fix(Date1) * 24 + Hour(Date1))
    ' Maintain a leading zero for the minute count.
    TextMinute = Right("0" & CStr(Minute(Date1)), 2)
    
    If Separator = "" Then
        Separator = FormatSystemTimeSeparator
    End If
    TextHourMinute = TextHour & Separator & TextMinute
    
    FormatHourMinute = TextHourMinute
 
End Function


' Obtain the system time separator without API calls.
'
' 2021-01-26. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatSystemTimeSeparator() As String

    Dim Separator   As String
    
    Separator = Format(Time, ":")

    FormatSystemTimeSeparator = Separator
    
End Function

 

[Gasman]

Create a function yourself using @arnelgp's code?

Solved - How to add fields with the Short Time format?

Hi I have a time report which contains a field formatted as Short Time. In the report there is a field for each day in the month. I need to make a Sum of the field, so I can se the total amount of time spend for the month. But it seems like the format "Short Time" can't chow more than XX:XX not...

www.access-programmers.co.uk

This is as simple as I could make it

tt=24*60*3.2
? tt\60 & ":" & tt mod 60
76:48

 

[Ken Sheridan]

The attached file illustrates a number of time arithmetic functions. In your case simply call the TimeElapsed function in your query:

SELECT
    tblDuties.PayPeriod,
    TimeElapsed(Sum(tblDuties.Overtime),"nn:ss") AS SumOfOvertime
FROM
    tblDuties
GROUP BY
    tblDuties.PayPeriod
ORDER BY
    tblDuties.PayPeriod;

This will return the sum of result of the summation in the format hh:nn:ss. If you don't need the seconds then change the second argument to "nn".

The file also includes a function to allow the duration between start and end time values which span midnight to be correctly computed where no dates are included in the values passed into the function.

 

[DHookom]

Actually, I would simply display the time in hours with decimals like 25.15 rather than 25:15.

 

[Ken Sheridan]

Actually, I would simply display the time in hours with decimals like 25.15 rather than 25:15.

Don't you mean 25.25, Duane? That would require the result of the summation to be multiplied by 24 as summing a set of values of date/time data type would result in the total number of days and fractions thereof. However, is 36.0833 as easy to visualise as 36:05? By all means do the maths in the original numeric values, but I'd present the results in time format.

 

[The_Doc_Man]

As you can see from the offerings of the other members, there is no problem in summing time - but there is a serious problem in displaying larger sums of time units. The FORMAT function (actually, family of functions) for VBA does not include a format like "hhh:nn:ss" - even though some other implementations of BASIC have such a thing. (VAX BASIC, e.g.) I won't offer you yet another solution. You have links and samples enough already.

From the form of your question, what you really need is a Public function to take in a time sum and produce a display-only string for the extended-hours format. You can pick one of the offered solutions as your basis. If you have a declared Public function, you can use it in a query for formatting purposes. In theory, the required gyrations CAN be done purely in SQL using integer math and truncation techniques in an SQL computational-heavy field - but it would be a real pain in the toches to get it right. A dedicated function would be quicker to build, easier to test, and easier to document.

OK, now you have chosen and implemented the formatting function you wanted... but here is ANOTHER "gotcha" brought about by Access/VBA limitations. That output string in hhh:nn:ss format cannot then be used as input to any text-to-time "standard" VBA input method, because it is not in an approved time format if it exceeds 24 hours. So whatever else you do, recognize that the time in the format you seek MUST be considered "for display ONLY". You must keep the original (numeric) time sum around if you want to do any more math with it.

 

[DHookom]

Don't you mean 25.25, Duane? That would require the result of the summation to be multiplied by 24 as summing a set of values of date/time data type would result in the total number of days and fractions thereof. However, is 36.0833 as easy to visualise as 36:05? By all means do the maths in the original numeric values, but I'd present the results in time format.

Yes, thanks for another catch of a pre-coffee post.

 

[E9-Tech]

@Ken Sheridan thanks for the example, managed to make it work, and thanks to everyone else for the imput!