Cumulative Timesheet Entries

[Duncs]

I have a timesheet in Excel, and users have the ability to work more
than their contractual hours each week. These 'additonal' hours, can
then be used to leave work early, start late etc. So, I may have an
entry as follows showing the total number of hours worked in a week:

Monday 09:05
Tuesday 09:05
Wednesday 08:50
Thursday 07:20
Friday 06:35

This should show a cumulative total of 03:55, showing that the person
has worked 03:55 more than their contractual hours. If the following
week, they show the following times:

Monday 07:00
Tuesday 07:50
Wednesday 07:20
Thursday 07:20
Friday 07:00

This would show that they were down 00:30 on the week but
cumulatively, they were up 03:25.

Can anyone help with this calculation?

Many TIA

Duncs

 

[Pete_UK]

It seems that the contracted hours per week is 37. In row 7 you can
put this:

Excess: =SUM(B1:B6) - 37/24

If B7 is formatted as time, it should show 3:55.

For the cumulative total you will need a Brought Forward row (use row
6), and just refer to the pevious week's total - I'm not sure how the
data is laid out for the two weeks, so I can't suggest a particular
formula.

Hope this helps.

Pete

 

[Duncs]

Pete,

Thanks for your reply however I don't think I explained the sheet /
layout very well.

The spreadsheet has 12 tabs, one for each month. For w/c 14th July,
an employee may have the following entries:

Start- 08:30 09:00 09:15 09:30 09:30
End- 16:30 17:15 18:30 16:15 17:00
Lunch- 00:40 00:40 00:40 00:40 00:40
Hrs Worked: 07:20 07:35 08:35 06:05 06:50
Hrs on Week: 36:25:00
+/- on week: -00:35

So, as you can see, the employee worked for 36 hours & 25 minutes
during the week, which left them 35 minutes short of their 37
contracted hours. However, in the previous week, the employee had
worked 2 hours & 25 minutes more than the 37 hours so, cumulatively,
they are 1 hour & 50 minutes up.

This all works well when the data is on the one sheet however, when
you move on to the next month, I start to get lost.

For example, if we take the first week in June as the starting point,
the employee has the following time data:

Week in month: 1 2 3 4
Hours worked in week: 40:55 36:40 37:05 37:05
+/- on Week: 03:55 -00:20 00:05 00:05

A cumulative +/- row would look like this:

Week in month: 1 2 3 4
+/- Cumulative: 03:55 03:35 03:40 03:45

So, the figure of 03:45 would be carried forward onto July's tab, and
the process would start again for July.

Does this make it clearer, or have I already answered my own question
by re-reading your reply and explaining my original problem better?

Duncs

 

[Pete_UK]

I still can't visualise how your data is laid out - presumably you
have a block of rows for an employee for one week, then a similar
block for another week, and again for week 3 etc, all within the
monthly sheet (and then repeat this for each employee).

Essentially, to get a cumulative number of hours you need to have a
"brought forward" entry, where for the weeks within the month you need
to link back to the previous week's total, and for the first week you
need to link back to the last week in the previous month's sheet.

Hope this helps.

Pete

 

[Duncs]

I think my problem was, as always, I was trying to overcomplicate
matters.

I was looking for a calculation that would total all hours from the
start of the year, then subtract the number of weeks since the start
of the year, multiplied by 37 hours in a week, to arrive at the figure
representing the number of hours that the employee is up or down,
cumulatively!!

Yes, very complicated.

However, if I only work on it a month at a time and simply carry over
the monthly value to the next sheet, it all works.

Cheers Pete

 

[Pete_UK]

You're welcome, Duncs - simple is usually best !! <bg>

Pete