Turning data into information with functions in OpenOffice Base

I've written an article for TechTarget on how to use the functions such as Sum and Max in OpenOffice.org Base queries and views.

It took me a while to grok how to use these -- the difficulty is not in applying the functions, but in the kind of data you can use the functions with, and the Group function that needs to be applied along with Sum, Max, or whatever you're using.

 

---

Comments

[Erick Snyder]

I am trying to make a time sheet that all I have to do is put in the time start and time finished with wor and have it put in the hours in another section, I have it set so that if I calculate the hours per day by hand it will give me the weekly hours I just can't get the spreadsheet to calculate the hours based on a military time format. if I put in 1900in one box then 0000 in another it will not come up with 5 for the hours. help me please

[Solveig Haugland]

Hi Erick,

Interesting issue. Let's see. I just did this in a spreadsheet and 5 did come up as the difference. Or at least 05:00. it looks like you need to:

- Format the time cells and the calculated cell displaying the hours worked with military format. Choose Format > Cells, click the Numbers tab, select Time in the left column and the HH:MM format which in 2.1 is at the top and displayed as 13:37.
- When you add up the hours worked, that field needs to be formatted the same way.
- Use the colon between hours and minutes to enter the time. 14:30 or 16:00, not 14.30 or 16.

This works for me in 2.1, taking the end time minus the beginning time.

HTH,

Solveig

[Justin]

I am struggling to have the spreadsheet calculate a timesheet. I have created a formula to add time (military format-HH:MM), calculate overtime and breaks. What I can not get it to do is calculate the total hours worked into a number format. If I leave the format as time (HH:MM) the time per shift will calculate but the total will not calculate past 24 (the program assumes 24 hour cycle and calculates as an A.M. time not a number value). No number format seems to calculate correctly

Sunday
Start Time ----- Break Time ----- Finish Time ----- Total Hours 00:00 (hours worked)
Overtime Hours 00:00
Regular hours 00:00

Any suggestions would be greatly appreciated-please e-mail if you would like the actual spreadsheet.

[Solveig Haugland]

Hi Justin,

I'm not sure of the solution of the top of my head, but perhaps try looking at the Functions? Click the Functions icon next to the Sum icon in the main Calc toolbar.

Solveig

[Alex]

Ya im also trying to get a time sheet working.. and ive got starting time. ending time. and then i want it to calculate to hours worked.. then multiply that by a fixed hourly rate.. to get total pay for the day.. But so far when I try to get my total hours worked.. It puts it in as a time instead of an hour amount. Then when I try to multiply that the math doesnt come out right.. I dont know how to fix it??

[Emi]

If you want to display the hours over 24 hours, use [HH]:MM format. That does the job.
For the rate calculation, I multiplied (# of hours * hourly rate) by 24. For some reason it converts the number of hours to "days" I guess.

[Neil]

Go to the following link for templates, including one for timesheets. Open it, unprotect it then look at the format for the cell containing the sum of the hours - number, general.

http://documentation.openoffice.org/Samples_Templates/User/template/index.html