Excel Wkbk can total hours 2 week increments

[hyancey]

I may be trying to do too much this time...

I need an excel workbook that will total time put in for hours (varying days or times when activities can happen) in 2 week increments for fellows in a program studying over a 2 year period (divided into quarters that are further divided into 2 week increments). They can easily see "supervised" versus "field hours" then to meet minimum requirements for their programs. The individual start dates will vary so they need to just start at any point in the year.

Not sure how to attach what I have already started with...

 

[mrmmickle1]

In order to share your data or mock data you will need to post it to a file sharing website like dropbox or filesnack and then post a link on the message board.

This would help greatly to understand your issue.

 

[hyancey]

Thank you! Gladly done...

https://www.dropbox.com/s/hcg44qu1r5oti9r/BCBA%20Hours%20Tracking%20Draft1.xlsx?dl=0

You can see the quarterly sheets are easy enough to work with and the data used for the drop down lists are in place but I can't wrap my brain around how to do the sheet described here.

 

[mrmmickle1]

If your data looks like this:

Excel 2012

	A	B	C	D	E	F	G
A (Ethical)	test #1
B (Def & Char)	test #2

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[TD="align: center"]Code[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Supervision Hours[/TD]
[TD="align: center"]Field Hours[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10/07/14[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]2.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10/08/14[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]3.00[/TD]

</tbody>

1st Quarter​

Then try setting up your data differently on List Data or Totals to reflect this:

Excel 2012
	A	B	C	D	E	F
1	Start	End	S (Supervised)	A (Ethical)	B (Def & Char)	ETC.
2	10/1/2014	10/15/2014	0.00	2.00	3.00
List Data

Cell Formulas
Range	Formula
B2	=A2+14
C2	=SUMIFS('1st Quarter'!$G$1:$G$10,'1st Quarter'!$A$1:$A$10,"<="&$B$2,'1st Quarter'!$D$1:$D$10,"="&'List Data'!C1)-SUMIFS('1st Quarter'!$G$1:$G$10,'1st Quarter'!$A$1:$A$10,"<"&$A$2,'1st Quarter'!$D$1:$D$10,"="&'List Data'!C1)
D2	=SUMIFS('1st Quarter'!$G$1:$G$10,'1st Quarter'!$A$1:$A$10,"<="&$B$2,'1st Quarter'!$D$1:$D$10,"="&'List Data'!D1)-SUMIFS('1st Quarter'!$G$1:$G$10,'1st Quarter'!$A$1:$A$10,"<"&$A$2,'1st Quarter'!$D$1:$D$10,"="&'List Data'!D1)
E2	=SUMIFS('1st Quarter'!$G$1:$G$10,'1st Quarter'!$A$1:$A$10,"<="&$B$2,'1st Quarter'!$D$1:$D$10,"="&'List Data'!E1)-SUMIFS('1st Quarter'!$G$1:$G$10,'1st Quarter'!$A$1:$A$10,"<"&$A$2,'1st Quarter'!$D$1:$D$10,"="&'List Data'!E1)

Change the A1:A10, G1:G10 and D1:D10 references to however long your data goes in the 1st Quarter Tab. If it was 100 rows then change them all like this in the formula:

A1:A100, G1:G100 and D1:D100

 

[hyancey]

Thanks! Let me try that out and let you know.