Cumulative Timesheet Calculations

D

Duncs

I have a spreadsheet recording employees worktimes—it seems to be all
the rage!

The format is as follows:
______________________________________________
Date | 08/06 | 09/06 | 10/06 | 11/06 | 12/06 |
Start | 08:00 | 08:00 | 08:00 | 08:00 | 08:00 |
End | 16:00 | 16:00 | 16:00 | 16:00 | 15:30 |
Lunch | 00:30 | 00:30 | 00:30 | 00:30 | 00:30 |
Total | 07:30 | 07:30 | 07:30 | 07:30 | 07:00 |
Weekly Total | 37:00
+/- For Week | 00:00
+/- Cumulative | 00:00

Each employee works 07:30 hours Monday – Thursday and 07:00 hours on a
Friday.

If an employee works an extra hour each night, their entry would look
like:
______________________________________________
Date | 08/06 | 09/06 | 10/06 | 11/06 | 12/06 |
Start | 08:00 | 08:00 | 08:00 | 08:00 | 08:00 |
End | 17:00 | 17:00 | 17:00 | 17:00 | 16:30 |
Lunch | 00:30 | 00:30 | 00:30 | 00:30 | 00:30 |
Total | 08:30 | 08:30 | 08:30 | 08:30 | 08:00 |
Weekly Total | 42:00
+/- For Week | 05:00
+/- Cumulative | 05:00

Rather a weekly cumulative total, I now need to show it as a daily
cumulative total. So, in the example above, it would look like:
______________________________________________
Date | 08/06 | 09/06 | 10/06 | 11/06 | 12/06 |
Start | 08:00 | 08:00 | 08:00 | 08:00 | 08:00 |
End | 17:00 | 17:00 | 17:00 | 17:00 | 16:30 |
Lunch | 00:30 | 00:30 | 00:30 | 00:30 | 00:30 |
Total | 08:30 | 08:30 | 08:30 | 08:30 | 08:00 |
+/- Cumulative | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 |
Weekly Total | 42:00
+/- For Week | 05:00

Then, the following week, the employee works as follows:
______________________________________________
Date | 15/06 | 16/06 | 17/06 | 18/06 | 19/06 |
Start | 08:00 | 08:00 | 08:00 | 08:00 | 08:00 |
End | 15:00 | 16:15 | 16:00 | 16:45 | 16:30 |
Lunch | 00:30 | 00:30 | 00:30 | 00:30 | 00:30 |
Total | 06:30 | 07:45 | 07:30 | 08:15 | 08:00 |
Weekly Total | 42:00
+/- For Week | 05:00

In this case, the +/- Cumulative line should take into account the
final total from the previous week, 05:00, and look like this:

+/- Cumulative | 04:00 | 04:15 | 04:15 | 05:00 | 06:00 |

Can anyone provide a formula to achieve this?

TIA

Duncs
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top