How to sum over a range that automatically restarts when a condition is met?

I am trying to keep a spreadsheet of my clock-in and clock-out times so that I have an idea of how much money to expect on future paychecks and to double-check my employers math. I currently have it setup like this:

  • Column A: Date of shift
  • Column B: Day of Week of Shift
  • Column C: Clock in time
  • Column D: Clock out time
  • Column E: Hours:Min worked
  • Column F: Hours:Min worked this week
  • Column G: Hours:Min worked this
  • Column H: Estimated Gross Pay
  • Column I: Estimated Net Pay

For columns F-I I can’t figure out how to have the ranges for the formulas autorestart. For example in column F I need a formula that sums the hours worked for the week and then start the sum over when the week changes. It needs to use the dates in column A to determine when a week restarts because the number of shifts in a week isn’t constant.

Any help on how to do this is greatly appreciated!

Start a running total in F2: =E2+IF(WEEKNUM(A2)=WEEKNUM(A1);E1;0)

Copy down.

If the suggested formula doesn’t do the job, attach a copy of your file to the question so we can see what you are working with. Use the paperclip tool to attach a file.

Thank you that did it! I used variations for the other columns. Thanks for your help!

Note that if you operate on proper spreadsheet time values and accumulate from week to week without ever resetting, there is a rounding error which will eventually show up as a minute lost or gained. For data on a single worker this is likely to show up after 2-3 years of continued registration, I guess. If you are managing a larger work force, the error may show up earlier.

Using MROUND(xxx;1/1440) on accumulated hours will round “to the minute” and so clean up the sum to remove the accumulation of rounding errors. (1440 is the number of minutes in 24 hours.)