COUNTIFS between date and time and criteria

[s2l]

Hello, I am trying to count the amount of time's in between two dates per hour

I am currently using: =COUNTIFS(B1:B10,F1,A1:A10,">="&D1+G1,A1:A10,"<"&D1+G2) Which will only count 1 date per hour. G1 is the first hour, G2 is the second hour. D1 is the first date and would like E2 as the 2nd date. F1 is the user

Any help or suggestions is much appreciated!

	A	B
2	3/1/16 8:58 AM	BOB
3	3/1/16 8:58 AM	BILL
4	3/1/16 3:52 PM	BOB
5	3/3/16 10:52 AM	BOB
6	3/4/16 9:52 AM	BILL
7	3/5/16 8:58 AM	BOB
8	3/6/16 9:28 AM	BILL
9	3/7/16 3:58 PM	BOB
10	3/8/16 8:58 AM	BOB
11	3/8/16 2:50 PM	BOB

[dflak]

I was tempted to pass this one up because there was no sample workbook attached. Most responders here don't want to take the time to create a workbook and populate it with data so they can work on your problem. The easier you make it for people to see and work on the problem, the more likely they will be to do it. Yes, I know this sounds harsh, but it's human nature. Fortunately I was able to copy and paste this into a workbook.

Attached is what I think you want to do. I had to make an assumptions that these are punch in and punch out times and that a person will punch in and punch out on the same day. I worked the issue using a series of helper columns. You can, if you want, consolidate the formulas in the helper columns until they only reference the original columns. I left them extended to show the intermediate steps in the logic.

Colum C has the formula: =INT(A2) what this does is strip the time off the date / time in column A. It makes the value a whole day only. This is needed to go along with the assumption that a person clocks in and out on the same day.

Column D has the formula: =B2&"-"&C2 what this does is concatenate the name and the date together in what I call a "composite key." It's a good technique when you have to consider more than one thing to make a decision on the contents of more than one cell. In this case we want to look at the combination of the person and the day the person punched in or out.

Column E has the formula: =MATCH(D2,D:D,0) this formula looks at the contents of cell D2 and returns the first row on which the value in D2 was found.

Column F has the formula: =E2=ROW() this formula evaluates to true if the value in E2 is equal to the row on which the formula resides. In this case E2 is the first row on which D2 was found. So the formula is true if the row on D2 is also the same row that the value in D2 was found. If it isn't, it means that the value in D2 was found in some previous row and the formula is false.

Column G has the formula: =IF(F2=FALSE,A2-INDEX($A$1:$A$11,E2,1),"No Start") If the value in D2 isn't the first time it was found, then it means that it was found on a prior row. What this means is that the person already has a record for that day. That is, the person had clocked in already. So this value must be the clock out. So we subtract the clock in time from the clock out time. The clock out time is the value in column A. The clock in time is the first time the persons name-time combination was found. This is the value in column E. We use Index to find that value.

If the value is TRUE then it means this is the first time this name-time combination was found and it can't be a clock out, so don't do any calculations on it.