Collecting Information based on dates

[tycasey17]

I would like assistance on how to be able to collect the information from this table for the dates between 1-1-2013 thru 1-7-2013 (basically a 1 week time period) to sum up the man hours, for each mechanic.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]MECHANIC
[/TD]
[TD]DATE
[/TD]
[TD]MAN HOURS
[/TD]
[TD]WORK
[/TD]
[/TR]
[TR]
[TD]Smith, John
[/TD]
[TD]1-1-2013
[/TD]
[TD]5
[/TD]
[TD]Trans
[/TD]
[/TR]
[TR]
[TD]Doe, Jane
[/TD]
[TD]1-7-2013
[/TD]
[TD]15
[/TD]
[TD]Engine Rebuild
[/TD]
[/TR]
[TR]
[TD]Doe, Jane
[/TD]
[TD]1-15-2013
[/TD]
[TD]1
[/TD]
[TD]Oil Change
[/TD]
[/TR]
[TR]
[TD]Smith, John
[/TD]
[TD]1-16-2013
[/TD]
[TD]1
[/TD]
[TD]Oil Change
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Does one of the options below do what you are looking for?
Option 1 will sum the the total work hours between 2 dates.
Option 2 would sum the man hours between 2 dates and each mechanic.

Excel Workbook
	A	B	C	D	E
1	MECHANIC	DATE	MAN HOURS	WORK
2	Smith, John	1/1/2013	5	Trans
3	Doe, Jane	1/7/2013	15	Engine Rebuild
4	Doe, Jane	1/15/2013	1	Oil Change
5	Smith, John	1/16/2013	1	Oil Change
6
7
8	Beg. Date	1/1/2013		Total Man Hrs.
9	End Date	1/7/2013		20
10
Option 1 - Sums toatal hrs. betwen dates

Excel Workbook
	A	B	C	D	E
1	MECHANIC	DATE	MAN HOURS	WORK
2	Smith, John	1/1/2013	5	Trans
3	Doe, Jane	1/7/2013	15	Engine Rebuild
4	Doe, Jane	1/15/2013	1	Oil Change
5	Smith, John	1/16/2013	1	Oil Change
6
7
8		Beg. Date	End Date
9		1/1/2013	1/15/2013
10	MECHANIC	Man Hours
11	Smith, John	5
12	Doe, Jane	16
13
Option 2 Man Hrs. for each mechanic between 2 dates

 

[tycasey17]

Thank you for your help!

I am more interested in option 2 that you did and I copied it verbatim to a excel and made sure the formulas matched the cells. It didn't add up the numbers correctly and looking at the formula, not knowing much about this function it looks like it should be showing only the certain numbers based on the formula. The issue I see is that the range is not recognizing anything but the beginning and end date. If you have any thoughts on the matter I am open to the help.

 

[AhoyNC]

Option 2 is looking at the names in cells A11 and A12 plus the beg. and end dates B9 and C9.
The --($A11=$A$2:$A$5) looks to see if any of the names in the range $A$2:$A$5 match the name in A11. It will return (TRUE, FALSE, FALSE, TRUE). We turn the TRUE and FALSE into 1 & 0 by the double negative --.

Make sure you have the proper cell references. $A11 locks the column, but not the row as you copy the formula down. $A$2:$A$5 locks the range the data is in. You will need to change for data ranges.

If the above doesn't help, please advise what number you are getting from the formula you entered and what it should be.

 

[tycasey17]

The ranges are all fine, it is reading the date that is the issue. If the beginning date is: 1/1/2013 and the end date is: 1/15/2013, the only calculation that gets done is based on those two dates only.

I even double checked the formulas based on what you gave me and they are correct.

 

[AhoyNC]

Take another look at the formula and make sure that the part for dates is showing > = in for the beg. date and < = for the end date (note there is no space between the < and = in the formula. Sounds like your formula only has = in the formula as the above formula worked for me in the above post.

<colgroup><col width="599"></colgroup>
[TD="class: xl63, width: 599"]=SUMPRODUCT(--($A11=$A$2:$A$5),--($B$2:$B$5 >= $B$9),--($B$2:$B$5 <= $C$9),$C$2:$C$5)
[/TD]

If you are using Excel 2010 you could use the the SUMIFS function.

Excel Workbook
	A	B	C	D	E
1	MECHANIC	DATE	MAN HOURS	WORK
2	Smith, John	1/1/2013	5	Trans
3	Doe, Jane	1/7/2013	15	Engine Rebuild
4	Doe, Jane	1/15/2013	1	Oil Change
5	Smith, John	1/16/2013	1	Oil Change
6
7
8		Beg. Date	End Date
9		1/1/2013	1/15/2013
10	MECHANIC	Man Hours
11	Smith, John	5
12	Doe, Jane	16
13
Sheet