Calculating hours has always been a challenge when working with spreadsheets. The reason for this is that most programs do not directly calculate the hours, they usually just join them in days. Here is an simple example – instead of 48 hours you get – 2 days. So if you need to calculate salary on hour basis, you will hit some problems.

Here is a simple tip that might help you work around with hours in Open Office.

Lets look at the following example:

You have a series of cells with hour values which are added into a final result of 46 hours. Now you need to convert these 46 hours into a simple integer. You can use the HOUR function – but it will only work for hours less than 24. After the 24-th hour the function will output 1 for 25 and 49 hour, 2 for 26 and 50 hour and so on.

Here is how I work it around. Lets say we add the hours from the A column and put it in B1:

=(SUM(A2:A10) – here A2 through A10 are the added hour fields they must be in the following format [HH]:mm:ss

I set the total hours field B1 in General Number format (Right Click on the cell -> Format Cells -> Numbers -> Number -> General ) then I call the absolute value of that cell:

=ABS(SUM(A2:A10))

the formula outputs a representation of the days. For our example it would be:

1,92 – this number actually represents one day and .92

In another cell lets say B2 I multiply the number of days by 24

=B1*24 – this will output a number (in this case 46) where B1 is the number of days.

You can of course directly in B1 itself multiply by 24. This will also get the total number of hours in a spreadsheet even when the hours are more then 24. So the formula in B1 that adds the hours in the A column and returns a number (do not forget about the required cell number format) representing the total hours will be:

=ABS(SUM(A2:A10))*24