How to set up a search function which will poplute a series of cells based on Countifs function
I am trying to populate a series of calendar/time cells which show whether a test activity was being performed based a countifs formula.
In columns c and d I have the start date/time and end date/time of each test activity.
In columns h and I have a the start and end dates/times (in one minute increments) that I want to determine if there was test activity in.
In column j I have a formula
=COUNTIFS(StartDate,">="&H
that I intend will go compare each of the test events for to see if they happened on or after the calendar date/time in column h and on or before the date/time in column I. The intent is to populate column j with true values when there was test activity during that 1 minute increment for all of the dates/times listed in columns h and I.
This is probably simple and I'm overlooking something.
Thanks for your help.
Jim
CountifsProblem.xlsx
In columns c and d I have the start date/time and end date/time of each test activity.
In columns h and I have a the start and end dates/times (in one minute increments) that I want to determine if there was test activity in.
In column j I have a formula
=COUNTIFS(StartDate,">="&H
that I intend will go compare each of the test events for to see if they happened on or after the calendar date/time in column h and on or before the date/time in column I. The intent is to populate column j with true values when there was test activity during that 1 minute increment for all of the dates/times listed in columns h and I.
This is probably simple and I'm overlooking something.
Thanks for your help.
Jim
CountifsProblem.xlsx
ASKER
I'm sorry. I probably didn't explain the question well. I am not looking for overlaps.
I am looking to see if the time defined in each set of cells: c6-d6, c7-d7; etc. happened in the times listed in the time ranges defined in cells hx (start time) - ix (end time). There are definitely times in the time range that I have defined in h and I but you have to go down to row 724 or so.
The intent is that I will have a "calendar" of 1 minute increments in h and I that shows when test activity was taking place.
Jim
I am looking to see if the time defined in each set of cells: c6-d6, c7-d7; etc. happened in the times listed in the time ranges defined in cells hx (start time) - ix (end time). There are definitely times in the time range that I have defined in h and I but you have to go down to row 724 or so.
The intent is that I will have a "calendar" of 1 minute increments in h and I that shows when test activity was taking place.
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help and sorry for the delay in the response to show this as "solved".
I want column J to be TRUE if there is an overlap between the timeframes defined by
1. Column D to Column D
2. Column H to Column I
If so, try this formula for J6 and copy down
=AND(I6>TestData[[#This Row],[Start date]],H6<TestData[[#This Row],[End date]])
Please note that in your sample data, I did not see any overlaps.