Hi,
The events have a start time and end time (as shown in the columns). I am trying to find out which events occur between 8 PM and 8 AM.
The problem is that the event doesn't have to start or end between 8 PM and 8 AM. As an example, if an event starts at 7 PM and ends at 9 AM, a formula tracking starting and end time does not capture the event as having occurred.
I have attached a sample of the data.
Thanks in advance.
Hi excellinginlife and welcome to the forum.
Does this do what you want? In C2:C12Formula:
Please Login or Register to view this content.
Row\Col est begin time est end time
Dave
Hi FlamingRetired,
Thanks for the quick response! That formula is helpful to see the time in between the start and end date but doesn't accomplish the goal.
To clarify, if there is any overlap between the start and end hours that fall between 8:00PM and 8:00AM, I would like the formula to return "TRUE", if not then "FALSE." As an example, C2 should be True because the event ended at 8AM, which would fall in the range of 8PM-8AM. C4 should return False, no period between the start and end fall between the required time frame.
The end goal is that I want to know if an event occurs between 8PM and 8AM given the start and end periods. In some cases the difference between the start and end date can be over 24 hours, those should always return "TRUE"
Thanks again!
Withdrawn by FR.
Last edited by FlameRetired; 09-18-2016 at 12:27 AM.
Sorry about that. There was a mistake in my previous (withdrawn) formula.
With the decimal times for 8PM and 8AM in C1:D1 this formula in C2 filled down.Formula:Please Login or Register to view this content.
Row\Col est begin time est end time
This would be a shorter formula with the same set-up helper row.Formula:Please Login or Register to view this content.
Another withdrawn.
Last edited by FlameRetired; 09-18-2016 at 04:09 AM.
Thank you so much. Those mostly work though I did find that the second formula using MMULT is more accurate. There was a case where the the end time was 8PM and both formulas returned false thought it should be true since it ends at 8PM and that would be within the range- the start and end times respectively were 1/13/2000 12:00 1/13/2000 20:00
really appreciate the help, I've been spending days on this and couldn't get as far as you! If you don't mind how does that second formula work? I looked up the functions but not sure how they work together.
Last edited by excellinginlife; 09-19-2016 at 12:15 PM.
Yes.
Still needs work.
Last edited by FlameRetired; 09-19-2016 at 12:47 PM.
This seems to cover everything.
In addition to the same helper row two helper columns. This helper formula in C2:D15.Then this formula in E2 filled down.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Row\Col est begin time est end time
Hm, the new formula seems to show some which should be True as false when the start and end times are after 8PM and end before midnight. I've attached a larger sample set which shows some of these highlighted.
2/13/2000 23:10 2/13/2000 23:10
2/13/2000 23:15 2/13/2000 23:15
1/16/2000 21:00 1/16/2000 23:00
1/16/2000 21:00 1/16/2000 23:00
Yes.
There are two approaches in the attached.
The first creates 3 more helper columns that parse the array in the previous formula into columns E:G. Entered in E2 filled down and across:The final formula in H2 filled down is:Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
On the other hand if you prefer to skip the additional helper columns (though they can be hidden for aesthetic purposes) this mega formula in I2 filled down does the job:Formula:Please Login or Register to view this content.
While I didn't check every row I did a fairly thorough filtering check. It works on everything I've seen so far.
I must say this one turns out to be more challenging than it first appeared.
See what you think.
excellinginlife,
In rep PM you indicated that it's almost perfect.
Does it need some more work?
Another way ...
=OR(MOD(A2,1)>20/24,MOD(B2,1)>20/24,MOD(A2,1)<8/24,MOD(B2,1)<8/24,INT(A2)<>INT(B2))
OOps sorry I forgot to send the reply. Still found a couple which returned FALSE even though it should be true. Thanks again. Something im trying instead is filtering to include anything with a time span over 12 hrs= true. And anything with start and end time between 8:01AM to 7:59PM is false.
1/3/2000 20:00 1/3/2000 20:00
6/18/2000 20:00 6/18/2000 20:30
Looks like Phuocam nailed it.
Also I kept assuming same time / same dates would be FALSE since there is zero time range for events to occur.
Thank you both. That last formula looks like what I need!
Yes the data shouldn't show like that but even with no time for the event to occur the formula should return true if it hits on 8:00 AM / 8:00PM. So the last formula actually doesn't work :/ Examples like
3/22/2000 8:00 3/22/2000 12:00
3/21/2000 8:00 3/21/2000 16:00
should return true.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks