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