I know there are many similar topics here asked similar question but could not find actually what am I looking for.
Now I have a data array like this;
Column A and B are entry/exit hours to the company respectively and they are formatted as "time".
Column C is also formatted as "time" and calculated using this formula:
IF(B1>A1,B1-A1,IF(B1<A1,B1-A1,0))
to calculate how many hours a person worked using the entry and exit hours in the array.
Column D is formatted as General and includes the company name of person working, which I only need.
What I try to do is a table like this;
Here Column A is the number of hours people work in one day, starting from 5 hours per day.
To calculate the number of persons worked certain amount of hours per day, I had to choose range of hours, for example for 5 hours per day, I said "people who work 4:45 hours to 5:14. So anyone in between this hours should be counted as they worked 5 hours per day. I did the similar approach for other hours.
Now the formula I created for this for column R4 is;
COUNTIFS('06.08.2017'!$C$1:$C$300,">="&TIME(4,45,0),'06.08.2017'!$C$1:$C$300,"<="&TIME(5,14,0),'06.08.2017'!$D$1:$D$300,"COMP1")
and for T4;
COUNTIFS('06.08.2017'!$C$1:$C$300,">="&TIME(4,45,0),'06.08.2017'!$C$1:$C$300,"<="&TIME(5,14,0),'06.08.2017'!$D$1:$D$300,"COMP2")
This I repeat for every time interval and I get a result. And it is very similar. In fact total number of person are correct for COMP2. BUT they are not for COMP1.
I checked almost everything came into my mind but could not find where I make mistake.
Update: I tried to catch where the issue is, and narrowed it down to which values it does not calculate. Result is; I found one of the issues, but I could not manage to find the second one.
Here are the new pictures:
and
First issue was, Row 208. In the Row 208 the time is 00:19 and using the formula I mentioned above in the Column C, the result was something like #####. So I had to add the date 01/01/1990 manually to make formula give me result. The result I get is as you can see 15:52:00, which is correct. But COUNTIFS formula I used in the result page does not count this, so I had to write this date manually also to make it count. Hence I got 1 of the missing person. And if you can help me with this problem to solve it automatically rather than changing adding 01/01/1990 first and then writing the time manually, I would appreciate it.
Now there is another person missing from the result table. Since data range is small, I manually categorized each time range with a color and compared them with the result table.
The problem cell is R10, this number should be 4. But it is 3. So I manually checked which one is not counted from the data table, and it is coming from the Row 222. My first thought was it is related with time, so I tried various time within the same range, but it did not change. But when I rewrite the "COMP1" in D222, it worked.
But why? That is my question. I have lots of data range like this I need to process so it is too much time consuming for me to try to catch problem like this every time, so I need to find the root cause. I used a Macro to generate the Column D. There are two group of people in the company one is named "XYZ'" and other one is "XYZ-B" and I wanted to convert them into one company named "COMP1". Here is the macro:
Sub ChangeCompanies()
'
' ChangeCompanies Macro
'
'
ActiveCell.Offset(0, 0).Columns("A:A").EntireColumn.Select
Selection.Replace what:="XYZ'", replacement:="COMP1", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace what:="XYZ-B", replacement:="COMP1", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Why it is working for every other cell but not this one?
COUNTIFS('06.08.2017'!$C$1:$C$300,">="&TIME(5,15,0),'06.08.2017'!$C$1:$C$300,"<="&TIME(5,44,0),'06.08.2017'!$D$1:$D$300,"COMP1")- E.Turanlı