Ask Your Question

addition of hours

asked 2016-03-16 13:08:12 +0200

Fowey gravatar image

I have tried using the sum function for hours but it does not produce a correct answer and always undercounts. Why is this and what can I do to correct it?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-03-16 16:22:17 +0200

Ratslinger gravatar image

updated 2017-10-05 18:24:30 +0200

You can sum a series using: =SUM(HOUR(A1:A3)) What is needed for a correct result is to change the format of the cell where the calculation is. If you want HOURS only, the format Category is Time and on the Format Code line enter: `[HH]`

Edited for clarity. As noted, question was tagged as Base not Calc.

Thank You karolous my overlooked fault.

For Base, in the form properties, Data tab 'Content type' = SQL command. Then in the 'Content' field goes your SQL Sum statement such as : SELECT SUM("Id_Bike") FROM "Bike" You can generate this by using ellipsis (...).
Where "Id_Bike" is the field you want to sum & "Bike" is the table name where the field is located. Then in the field where you want this result, in the properties Data tab, select the 'Data field' (in this case): SUM("Bike"."Id_Bike") .

For totaling hours in a time field you want something like: SELECT SUM(HOUR("MyTime")) FROM "TimeCalc".

To add minutes to the calculation (partial dropped) :

SELECT SUM(("TotalMinutes" / 60) + "TotalHours") as "HoursTotaled" From (SELECT SUM(Minute ( "Time" )) as "TotalMinutes" , SUM(Hour ( "Time" )) as "TotalHours" FROM "TimeCalc")

Based upon your latest response (please use comment to reply), the following will give a total of Hours and Minutes in a format of HH:MM

`SELECT CONCAT("HRS","MIN") AS "TTLTIME" FROM (SELECT CONCAT(((SUM(Minute ( "Time" )) + SUM((Hour ( "Time" )) * 60))  / 60), ':') AS "HRS",  MOD((SUM(Minute ( "Time" )) + SUM((Hour ( "Time" )) * 60)) ,60) AS "MIN" FROM "TimeCalc")`

To further clarify I've included a simple sample with a table of time elements and a form with the resulting total:TimeTotal.odb.

edit flag offensive delete link more


The Question is tagged with base

basee != calc

karolus gravatar imagekarolus ( 2016-03-16 16:47:33 +0200 )edit

answered 2017-10-05 15:02:51 +0200

Acdane gravatar image

updated 2017-10-05 15:05:29 +0200

ok I figured out a simple solution. This is my work that I am using. I was doing a time card. ColB3 is start time and ColC3 is end time. Format set for these columns is hh:mm:ss PM or the option that says 01:37:46 PM.

The in Col D total time (ColC-ColB)times 24 as in 24 hours, (C3-B3)*24. Then do a sum function as usual to add all the total hours up but you need to change the function cell format to Number General. Works like a charm!

edit flag offensive delete link more

answered 2016-03-25 12:00:55 +0200

Fowey gravatar image

Thank you for your answer. I realise that if I just wanted whole hours I could define the field as numeric, but to add hours and minutes as HH:MM to get an answer in the same form as you say does not do it. I am thinking of trying 2 separate columns one for hours the other for minutes and define each as numeric and do the conversion in the SUM expression. Might that work?

edit flag offensive delete link more


Yes, this is the most easiest way. I build a lot of applications in ms-acces this way (engineer & sales activities)

Michel_1495 gravatar imageMichel_1495 ( 2016-04-11 18:06:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-03-16 13:08:12 +0200

Seen: 2,825 times

Last updated: Oct 05 '17