« More on the Data Pilot: Analyzing a Data Source/Database | Main | Restarting Numbering in OpenOffice Writer: Yes, Virginia, There Is a Way to Do This Without Manual Labor »

November 08, 2006


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

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.



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

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.

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.


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??

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.

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.


The comments to this entry are closed.

GetOpenOffice Consulting

Get Book Resources

Search This Blog