summarizing timesheets

[nbirnbaum]

I'm trying to automate calculation of overtime pay for employees.

Employee timesheets are exported from our time system as Excel spreadsheets.

They contain two weeks of data. Each day of the week might have multiple time entries. Here's an example of part of a spreadsheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Billable Hours[/TD]
[TD]Nonbillable Hours[/TD]
[/TR]
[TR]
[TD]7/1/2017 9:30 AM[/TD]
[TD]3.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2017 2:30 PM[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7/1/2017 5:00 PM[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/2/2017 10:00 AM[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7/2/2017 3:00 PM[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to:
1. Determine the total time worked for each day.
2. Determine the total time worked for each week (remember there are two weeks of date represented in the spreadsheet).

Can you make suggestions? Thanks!

 

[Dave Patton]

Try Sumifs or SumProduct

Excel 2010
	B	C	D	E	F	G	H	I
2		Billable	Non-Billable	Total
3	Jul 01, 2017 09:30	3.50		3.50		Jul 01, 2017	9.50	9.50
4	Jul 01, 2017		2.00	2.00		Jul 02, 2017	11.00	16.00
5	Jul 01, 2017	4.00		4.00
6	Jul 02, 2017		2.00	2.00		Week
7	Jul 02, 2017		4.00	4.00		Jul 01, 2017	45.50	45.50
8	Jul 02, 2017	5.00		5.00
9	Jul 03, 2017	5.00		5.00		TRUE
10	Jul 04, 2017	5.00		5.00
11	Jul 05, 2017	5.00		5.00
12	Jul 06, 2017	5.00		5.00
13	Jul 07, 2017	5.00		5.00
14	Jul 08, 2017	20.00		20.00
6a

Cell Formulas
Range	Formula
B3	=DATE(2017,7,1)+TIME(9,30,2)
E3	=C3+D3
H3	=SUMPRODUCT(--(INT(B3:B101)=G3),(E3:E101))
H4	=SUMPRODUCT(--(INT(B4:B102)=G4),(E4:E102))
H7	=SUMPRODUCT(--(INT(B3:B101)>=G7),--(INT(B3:B101)<=G7+6),(E3:E101))
I3	=SUMIFS(E3:E101,B3:B101,">="&G3,B3:B101,"<="&G3+1)
I4	=SUMIFS(E4:E102,B4:B102,">="&G4,B4:B102,"<="&G4+1)
I7	=SUMIFS(E3:E101,B3:B101,">="&G3,B3:B101,"<"&G3+7)
G9	=SUM(C:C)+SUM(D:D)=SUM(E:E)

 

[Dave Patton]

correct previous post
I had problems posting to the forum.

Excel 2010
	B	C	D	E	F	G	H	I
2		Billable	Non-Billable	Total
3	Jul 01, 2017 09:30	3.50		3.50		Jul 01, 2017	9.50	9.50
4	Jul 01, 2017		2.00	2.00		Jul 02, 2017	11.00	11.00
5	Jul 01, 2017	4.00		4.00
6	Jul 02, 2017		2.00	2.00		Week
7	Jul 02, 2017		4.00	4.00		Jul 01, 2017	45.50	45.50
8	Jul 02, 2017	5.00		5.00
6a

Cell Formulas
Range	Formula
B3	=DATE(2017,7,1)+TIME(9,30,2)
E3	=C3+D3
H3	=SUMPRODUCT(--(INT($B$3:$B$100)=G3),($E$3:$E$100))
H4	=SUMPRODUCT(--(INT($B$3:$B$100)=G4),($E$3:$E$100))
H7	=SUMPRODUCT(--(INT(B3:B100)>=G7),--(INT(B3:B100)<=G7+6),(E3:E100))
I3	=SUMIFS($E$3:$E$100,$B$3:$B$100,">="&G3,$B$3:$B$100,"<"&G3+1)
I4	=SUMIFS($E$3:$E$100,$B$3:$B$100,">="&G4,$B$3:$B$100,"<"&G4+1)
I7	=SUMIFS($E$3:$E$100,$B$3:$B$100,">="&G7,$B$3:$B$100,"<"&G7+7)