Error In Adding Time

[Eranka]

Hi All



When i add the "Accepted_Hours" (as per the below pic) the total should show as 32:22 instead its showing as 8:22, how can i rectify this error. Please help.

[June7]

Provide the query SQL statement.

[Eranka]

i just typed =Sum([Accepted_Hours])

[CJ_London]

hours has a maximum of the number of hours in a day. Dates are a special form of double which are formatted to look like a date for the area of the world you are in.

The bit before the decimal point represents the date (a number where 21/12/1899 is 0 and each day is incremented by 1 - today is 43091), the bit after the decimal point represents the time in seconds as a fraction of the number of seconds in a day (86400). So when you calculate a time difference (say between 9am and 12noon) this will be 0.5-0.375=0.125. If you have say 10 of these added together you will total 1.25 - which represents a day and 6 hours. You are seeing the 6 hours, not 30 hours.

So you need a formula to convert the 1 part to hours. Easiest would be to multiply by 24 - so 1.25*24=30

so try sum([accepted_hours])*24 and remove the date formatting. This will give you something like 32.366

but now you need to convert the .366 to provide minutes

To get the hours you would use the int function int(sum([accepted_hours])*24)

then add a colon to the end

then get the fraction to calculate the minutes

(sum([accepted_hours])*24)-int(sum([accepted_hours])*24)

and multiple by 60

((sum([accepted_hours])*24)-int(sum([accepted_hours])*24))*60

so your total formula becomes

int(sum([accepted_hours])*24) & ":" & ((sum([accepted_hours])*24)-int(sum([accepted_hours])*24))*60

[Minty]

Your adding time parts of stored dates - once it gets past 24 hours and your formatting it as a time you'll start to see it can't display it as a time per se.

To display it properly you'll need to get a bit obtuse. DateDiff("n",0,(Sum(Accepted_Hours))) will give you the total time in minutes.
I'll let you fathom out where to go from there.

Edit - Ajax gave you a much more complete explanation.

[Eranka]

Hi

I need the time in hh:mm so i modified your formula as DateDiff("h",0,(Sum(Accepted_Hours))) but it gives the out put as 32.00 not as 32:22. How to correct this?

or can use Round as below

Round(int(sum([accepted_hours])*24) & ":" & ((sum([accepted_hours])*24)-int(sum([accepted_hours])*24))*60,1)

[Eranka]

Hi Guys

Thanks a lot for your help.

I just modified the Ajaz Code as below

=Int(Sum([Accepted_Hours])*24) & ":" & Round(((Sum([Accepted_Hours])*24)-Int(Sum([Accepted_Hours])*24))*60,1)

above formula it rounds up the value to 32:22.

Thank You Very Much Guys.

[ArviLaanemets]

It is a mystery for me, why MS doesn't include into Access same format types as it uses in Excel - "[h]:mm"/"[m]"/[d] hh:mm"/etc.. In Excel you can display the difference how OP wanted (32:22).

[Eranka]

Hi Guys



Please see above image. I am adding the time in above image. when add the total time it should be 23:26 instead its showing 23:43. How do correct this?

The code i used is

=IIf(((Sum([Accepted_Hours])*24)<=24),(Sum([Accepted_Hours])*24),(Sum([Accepted_Hours])*24))

[Minty]

Your IIf has the same result for true and false e.g. (Sum([Accepted_Hours])*24)
Is that intended, it makes no sense?

Are you sure that is the code used ? Access will normally only add up the data as presented?

[Eranka]

Then i tired with this. still not working.

IIf(((Sum([Accepted_Hours]))<24),(Sum([Accepted_Hours])),(Int(Sum([Accepted_Hours])*24) & ":" & ((Sum([Accepted_Hours])*24)-Int(Sum([Accepted_Hours])*24))*60))