CODEWITHSUNDEEP
datetimems-accesssum
Volkan
Volkan

Reputation: 546

correct sum of hours in access

I have two columns in an access 2010 database with some calculated field: 

time_from time_until calculated_field(time_until-time_from)
10:45      15:00         4:15
13:15      16:00         2:45
11:10      16:00         4:50
08:00      15:00         7:00
08:00      23:00        15:00

Now so far, it is good: calculated field did its job to tell me total hours and mins... now, I need a sum of a calculated field.... I put in an expression builder: =Sum([time_until]-[time_from]) I guess total sum should give me 33:50... but it gives me some 9:50. why is this happening? Is there a way to fix this?

update: when I put like this:

=Format(Sum([vrijeme_do]-[vrijeme_od])*24)

I get a decimal point number... which I suppose is correct.... for example, 25hrs and 30mins is shown as 25,5

but, how do I format this 25,5 to look like 25:30?

Upvotes: 3

Views: 7816

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123799

Similar to the answer from @HansUp, it can be done without VBA code like so

Format(24 * Int(SUM(elapsed_time)) + Hour(SUM(elapsed_time)), "0") & ":" & Format(SUM(elapsed_time), "Nn")

Upvotes: 2

HansUp
HansUp

Reputation: 97131

As @Arvo mentioned in his comment, this is a formatting problem. Your expected result for the sum of calculated_field is 33:50. However that sum is a Date/Time value, and since the number of hours is greater than 24, the day portion of the Date/Time is advanced by 1 and the remainder 9:50 is displayed as the time. Apparently your total is formatted to display only the time portion; the day portion is not displayed.

But the actual Date/Time value for the sum of calculated_field is #12/31/1899 09:50#. You can use a custom function to display that value in your desired format:

? duration_hhnn(#12/31/1899 09:50#)
33:50

This is the function:

Public Function duration_hhnn(ByVal pInput As Date) As String
    Dim lngDays As Long
    Dim lngMinutes As Long
    Dim lngHours As Long
    Dim strReturn As String

    lngDays = Int(pInput)
    lngHours = Hour(pInput)
    lngMinutes = Minute(pInput)

    lngHours = lngHours + (lngDays * 24)
    strReturn = lngHours & ":" & Format(lngMinutes, "00")
    duration_hhnn = strReturn
End Function

Note the function returns a string value so you can't do further date arithmetic on it directly.

Upvotes: 3

Krish
Krish

Reputation: 5917

I guess you are trying to show the total in a text box? the correct expression would be =SUM([calculated_field_name]).

Upvotes: 0

Related Questions