Tracking hours and pay outstanding in spreadsheet

  • Hi all. Thanks for taking the time to read my post.
    I'm trying to track my hours using a spreadsheet app on my phone (Docs to Go). For the most part I began the sheet with simple format and formula and it worked fine. Being in winter I have to track two separate pay scales, standard labor and snow removal. On the Black River sheet I have column G computing hours for the day from columns E and F. Column H gave a running total for that week and started a new week on the condition column A of the previous row was sunday or week end. It became complicated when snow removal came in. I'm paid a different scale for snow so I wanted to track my hours for that separate from my normal work hours. I tried this in column K using J as the condition for snow. I kind of had it working okay, buy it's not calculating correctly in all. The snow hours seem fine. The standard hours are losing track of earlier hours that week if there is more than one consecutive snow day that week with no standard work. I was hoping a new set of eyes and more experienced skill might have some ideas? Maybe suggestions of corrections? I tried to figure out how to search for the beginning and end of that week and just sum those feilds, but what I've tried failed. That attempt was in column N and O. I thought I could count the number of rows for that week and subtract that from the last row number of that week. I couldn't find the function to do that with. I thought there was a function, thisrow or something, but when I look at available functions on this app I only find rows(), which I'm not sure how to use or what it does. I haven't looked it up yet. I can't open excel on my laptop because the laptop wanted a vacation I guess. I'm attaching my spreadsheet for review and comment or further questions from the experts.

  • Re: Tracking hours and pay outstanding in spreadsheet


    Okay, this is physically painful to look at, but I'll take a stab.


    I'm HOPING that your main issue is in column H, which is SUPPOSED to be a sum of the non snow hours done that week. I'll try to stay close to what you are doing formula wise, but some things just HAVE to be changed.


    Long (mostly English) answer, column H should be the cell above, + plus the cell to the left UNLESS, it's a weekend, day after weekend or snow day. If it's a weekend, it should just be cell above. If it's day after weekend, it should just be cell to the left. That much is working. I THINK you've done snow incorrectly. If it's a snow day you've told it to be 0, but you should really have told it to be day above (don't want to ADD snow hours, but want to carry forward work hours from above). Finally you then have issues with BOTH a snow day AND day after weekend, which would get you pretty screwed up nested ifs.
    There is a better approach. In english, H should be the cell above (unless yesterday was a week end) + cell to the left (unless today is a weekend, or a snow day), so........


    Short (mostly formula) answer 1: H271: =IF(OR(A270="week end",A270="sunday"),0,H270)+IF(OR(A271="week end",J271="snow"),0,G271)


    This APPEARS to work correctly in column H.