Including time/number ranges displayed in a chart

Hello,

Please see attached spreadsheet or screenshot.

I am working on a spreadsheet in which I have a certain length of session time added to the beginning of a session. There are a given number of sessions with a start date and time (start date and time can vary). I simply add the session length (which can also vary) to the session time in order to get the session end time. On the right side I have a chart (Session active window) in which I want to display the time ranges there is an active session. I manually typed in the time ranges to illustrate what I am looking for. So basically if sessions overlap, I want the output chart "Session active window" to display the time ranges in which a session is active. I've tried a few different things and tried researching different commands but cant figure out a way to do this. Since time is basically viewed as a number in excel, it can be looked at as overlapping numbers rather than time. Any help on this would be greatly appreciated. Thanks,

Cory

Please try

F4 press Ctrl+Shift+Enter and copy down

=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($C$4:$C$99)/IFERROR($C$4:$C$99>CEILING($D$3:$D$98,"0:01"),1),ROWS(F$4:F4))),"")

G4 copy down

=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($D$4:$D$99)/NOT((CEILING($D$4:$D$99,"0:01")>=$C$5:$C$100)*(CEILING($D$4:$D$99,"0:01")<=$D$5:$D$100)),ROWS(G$4:G4))),"")

Hi Cory,

One of the best ways to see what a formula is doing, use evaluate to step thru the breakdown of the formula.

Sometimes if the range is too large it will be hard to see in the Evaluate interface. If that is the case, shorten the range as a test.

Evaluate Formula

• Click on the cell with the formula >> Ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

Hi Cory,

$C$4:$C$99>$D$3:$D$98 is condition for Session begin, it has to be more than Session end of row before

Eg:
C7 9/12/2018 16:00:00 > D6 12/8/18 4:00 PM this is True
C8 10/12/2018 16:00:00 > D7 12/11/18 4:00 PM, this is False

CEILING($D$3:$D$98,"0:01") is use for round column D to minute as it come from D5 =C5+(C$1/24) this will have some second or milli-second
If check =D5=C6 is False

IFERROR( ,1) is for C4 than can't compare with D3 "Session end"

When condition are True or False

Row()/True = row()/1 = row()
Row()/False = row()/0 = #DIV/0

Aggregate(15,6, () , k)
15 for Small , 6 for ignore error like #DIV/0 will pull out row() that have True as condition, combine with index will give result that meet condition.

All you have to do is find condition that suit your need and put after / for divide.

Please try

F4 with CSE

=IFERROR(1/(1/INDEX(C:C,AGGREGATE(15,6,ROW($C$4:$C$99)/IFERROR($C$4:$C$99>CEILING($D$3:$D$98,"0:01"),1),ROWS(F$4:F4)))),"")


G4 copy down

=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($D$4:$D$99)/(NOT(CEILING($D$4:$D$99,"0:01")>=$C$5:$C$100)*(CEILING($D$4:$D$99,"0:01")<=$D$5:$D$100)+($D$4:$D$99=MAX($D$4:$D$99))),ROWS(G$4:G4))),"")