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

[DragonTamer]

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
  pay-period
• 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!

[keme]

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.

[DragonTamer]

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

[keme]

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.)