Hi,
I am trying to figure out when three students-Ryan, vince, and mike have been checking in to work, based on 30 minute increments for multiple dates. I just want a count of total checkins for all three. So for example between 5 and 530 am if all three checked in on jan 1, 2014, then my spreadsheet would return 3 and then on the 4th if 2 checked-in between 9 and 930 am it would return 2.
See the spreadsheet below.
Any help will help!
Hi,
Please refer to my attached file.
First, look at the Sheet Old, this is your original unmodified data. Click any cell in O3:O26, you will find that the cell is actually in date format although it shows up as time. For example if the cell O3 is selected, you will find at the formula bar that this cell content is 01/01/1900 4:35:36 AM, although it shows up as 4:35:36 AM. Since we will compare column O and column B, so both columns must be in the same format, in this case is time. Column B has already correct format (time), so we need to convert column O first before we can continue.
To convert this, put this formula at cell R2 :
then copy down until cell R26.
Please Login or Register to view this content.
Select range R2:R26, press Ctrl C (copy), then select cell O2, then Paste Special as Value.
Now column O is already has correct time format.
I copy the result of above process to the sheet New, so you can compare before and after.
Now, the main formula, put this formula at cell C2 :
RegardsPlease Login or Register to view this content.
1. I care dog
2. I am a loop maniac
3. Forum rules link : Click here
3.33. Don't forget to mark the thread as solved, this is important
That's great! Now I just have one more question.
What if I need to take this one step further and figure out where they checked in for work (what city?). I have a list of cities, and I need to know when they checked in to Chicago, New York, and LA. Ryan Vince and Mike might have checked in Chicago, New York, LA, Seattle, Phoenix, and SF but I don't care about SF, Seattle and Phoenix. I just want to know when they checked in given the previous variables in Chicago, New York, and LA.
I added cities in Column R of the spreadsheet the previous poster had.
I know this is complicated...but I appreciate all the help
Thanks for all your help!
Here is another suggestion, also with a helper...
R2=P2+MOD(O2,1) copied down
C2=COUNTIFS($R$2:$R$26,">="&C$1+$B2,$R$2:$R$26,"<"&C$1+$B3)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
With your last request, what would you see the results looking like?
Please refer to my uploaded file.
Since you want to show the city, it is possible that one cell must show multiple values.
For example, for cell C1 (5:00 AM , 01/01/2014), it is possible that ryan log in at New York, mike at L.A., vince at Chicago, all at the same time, so there are three possible values for this cell, unlike previous case which only have one value (that is count of occurence).
To overcome this, I set cell N2 (a validation cell) to choose one of three names (ryan, mike, vincent), so the table is set for one name only.
The formula at C2 :
RegardsPlease Login or Register to view this content.
Going back to the first excel sheet karedog posted. What if we wanted to filter out when vince checked in outside of normal hours--8am and 8pm on Monday through Fridays, Saturdays 10-5, and Sundays 12-3pm. We don't want to include vince in the normalhours just outside.
My initial thoughts were and IF statement outside of this checking either between or outside of the times. I just don't know how to clarify the row 1 with the dates. Any ideas. I'm thinking the following below is what it would look like.
=IF(..........,SUMPRODUCT(($O$2:$O$26>=$B3)*($O$2:$O$26<$B4)*($P$2:$P$26=C$1)*($Q$2:$Q$26={"ryan","vince","mike"})),SUMPRODUCT(($O$2:$O$26>=$B3)*($O$2:$O$26<$B4)*($P$2:$P$26=C$1)*($Q$2:$Q$26={"ryan","mike"})))
Thanks.
Not using if(), using a normal add operation instead.
For example, in a class, the teacher wants to know how many male children who have moustache and how many female children who have long hair. First, the teacher count how many male children who have moustache (let say X), then count how many female children who have long hair (let say Y). The teacher count this in seperate group, not mixing male with female children. The answer is just simple adding X with Y.
In your case, first count conditions meet for ryan and mike (let say X), and then count conditions meet for vince (let say Y), then adding this two numbers (X+Y).
The conditions for ryan and mike (X) is solved using former formula =.......{"ryan","mike"}....
But for vince (Y), it consist of many smaller formulas (Y1, Y2, Y3, ....) since this cannot be solved just using a single formula.
For example :
Y1 --> Vince, Monday to Fridays, < 8:00 AM
Y2 --> Vince, Monday to Fridays, > 8:00 PM
Y3 --> Vince, Saturday, < 10:00 AM
Y4 --> Vince, Saturday, > 5:00 PM
Y5 --> Vince, Sunday, < 12:00 PM
Y6 --> Vince, Sunday, > 3:00 PM
So the final formula would be :
= X + Y
= X + Y1 + Y2 + Y3 + Y4 + Y5 + Y6
This would be very long formula though.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks