Totaling a Sum for every 7 days

S

seanrigby

Have work time tracked on a spread sheet. On every Saturday, it begins
a new week.

I need a way to break it down per week for the hours worked, for the
entire month. Straight time and overtime included)

Our month begins on the 26th of every month, and ends the 25th as
well.

Is this possible?
 
B

Barb Reinhardt

Let's say your date are in A2:A5
The hours are in C2:C5
The workweek is in B2:B5 (calculated)
In B2 enter: =WEEKNUM(A2+1,1) and copy down
Column D are your cumulative hours for the month (calculated)
In D2 enter: =SUMPRODUCT(--(B$2:B2=B2),(C$2:C2)) and copy down.

Is this what you want???

HTH,
Barb Reinhardt
 
B

Barb Reinhardt

You could also add a conditional format for the Cumulative column. The
equation for the data as presented in my example would be

FORMULA IS: =COUNTIF(B$2:B$6,$B2)<>COUNTIF(B$2:B2,$B2)

Ensure you select from B2 down. Change the font color to white if the
condition is met.
 
S

seanrigby

Barb,

I have as follows:

Column A is the name of the day
Column B is the actual date
Column C is the strait time hours
Column D if my overtime hours

From this list, I need to know when each Saturday begins
and then total each straight time and overtime hours

For example:

A1: Wednesday B1: 6/28/06 C1: 8 D1:0
A2: Thursday B2: 6/29/06 C2: 8 D2: 0
A3: Friday B3: 6/30/06 C3: 7 D3: 1
A4:Saturday B4: 7/1/06 C4: 8 D4: 4


From this list, in a specific cells It would list the following:

6/28/06 to 06/30/06 23 Straight Time Hours & 1 Overtime Hour
7/1/06 to 07/01/06 8 Straight Time Hours & 4 Overtime hours

The outcome could be listed on a table. Doesn't really matter. Jus
want it automatic so I don't have to go calculate it each month.

Can you help?

thanks
 
S

seanrigby

Barb,

I have as follows:

Column A is the name of the day
Column B is the actual date
Column C is the strait time hours
Column D if my overtime hours

From this list, I need to know when each Saturday begins
and then total each straight time and overtime hours

For example:

A1: Wednesday B1: 6/28/06 C1: 8 D1:0
A2: Thursday B2: 6/29/06 C2: 8 D2: 0
A3: Friday B3: 6/30/06 C3: 7 D3: 1
A4:Saturday B4: 7/1/06 C4: 8 D4: 4


From this list, in a specific cells It would list the following:

6/28/06 to 06/30/06 23 Straight Time Hours & 1 Overtime Hour
7/1/06 to 07/01/06 8 Straight Time Hours & 4 Overtime hours

The outcome could be listed on a table. Doesn't really matter. Jus
want it automatic so I don't have to go calculate it each month.

Can you help?

thanks
 
S

seanrigby

Barb,

I have as follows:

Column A is the name of the day
Column B is the actual date
Column C is the strait time hours
Column D if my overtime hours

From this list, I need to know when each Saturday begins
and then total each straight time and overtime hours

For example:

A1: Wednesday B1: 6/28/06 C1: 8 D1:0
A2: Thursday B2: 6/29/06 C2: 8 D2: 0
A3: Friday B3: 6/30/06 C3: 7 D3: 1
A4:Saturday B4: 7/1/06 C4: 8 D4: 4


From this list, in a specific cells It would list the following:

6/28/06 to 06/30/06 23 Straight Time Hours & 1 Overtime Hour
7/1/06 to 07/01/06 8 Straight Time Hours & 4 Overtime hours

The outcome could be listed on a table. Doesn't really matter. Jus
want it automatic so I don't have to go calculate it each month.

Can you help?

thanks
 
B

Barb Reinhardt

Column A is the name of the day
Column B is the actual date
Column C is the strait time hours
Column D if my overtime hours
Click to expand...

Let's say your date are in B2:B5
The straight time hours are in C2:C5
Your overtime hours are in D2:D5
The workweek is in E2:E5 (calculated)
In E2 enter: =WEEKNUM(B2+1,1) and copy down
Column F are your cumulative straight time hours for the month (calculated)
In F2 enter: =SUMPRODUCT(--(E$2:E2=E2),(C$2:C2)) and copy down.
Column G are you cumulative overtime hours for the month
G2: =SUMPRODUCT(--(E$2:E2=E2),(D$2:D2))

This is a basically the same thing but with different column IDs.
 
Top