timesheet

carokann · Newbie Joined: 23 Apr 2009 Posts: 1

Hello all,

My first post here, and want to say thanks to all the info I have already learned. I am still just beginning but it felt good to actually accomplish what I wanted.... ( the long way i think)

Ok I searched "timesheet" and read alot of the threads, that along with google got me to finish my timesheet in calc, but I did it all what I think was the long way, and I am hoping someone here can help me to not have to manually change one of the formulas I have.

http://files.filefront.com/Timesheet+2009ods/;13643800;/fileinfo.html

is the file I made. My problem was that the pay period is bimonthly however overtime is based on a Sun-Sat 40 hour work week. To have the totals on each sheet correct I added a weekly total under every Sat. What took me awhile to figure out was the over time formula. I manually went through the year and rewrote the formula to ensure the correct cells were referenced.

However if I could get the weekly total formula to somehow total every Sun through Sat, then I wouldnt have to manually rewrite the formuals when the year changes, or even maybe do away with the weekly total row.....

Instead Of the "sum" of three "if" statements ( one of the 'if" statements has an "if"statement inside of it. I tried to learn how to use the "sumif" function however I couldnt come up with something that would more than just one day.

I hope I havent rambled and that its clear what I am trying to do. So any help is appreciated.

keme · Posted: Thu Apr 23, 2009 4:01 am Post subject:

You can do the calculations only on a specified weekday (like saturday) by making it conditional, based on the WEEKDAY() function.

Further, I guess your formulas can be simplified if you add a "intermediate sum" to the right of your form for the incomplete weeks, then perhaps an "overtime" row below the weekly sum for worked hours. Any intermediate calculations can be hidden if they clutter the display.

Myself, I'd probably use a strict tabular setup for raw data. That provides a calculation base that's much easier to work with. If you like, you can make that table fetch data from your half-month sheets. It's a bit of work to set up, but once you have it, it'll be more flexible than your current model.