How exactly does this formula work?

[iceandfire]

=SUM(IF((C3>J$3:J$50000)*(B3<K$3:K$50000)*(A3=I$3:I$50000),((IF(C3<K$3:K$50000,C3,K$3:K$50000)-IF(J$3:J$50000>B3,J$3:J$50000,B3))*24*60),0))

Formula above is set as an array.

Column C contains end date and time (e.g. 3/6/16 0:30:00) of a schedule pair.
Column J contains start date and time (3/5/2016 0:01) of LogInLogOut pair.
Column B contains start date and time of a schedule pair.
Column K contains end date and time of a LogInLogOut pair
Columns A & I contains employee IDs.

Please explain exactly how it works because while I understand that the formula basically sums the minutes that fit within the schedule, I don't know how it does that in a step-by-step manner.

Thanks in advance!

[JBeaucaire]

The the FIRST IF() function filters out rows that are completely outside of the range start/end times.

Then the SECOND IF() function is doing the work. It is reconstructing the array of time values creating a new table that changes the values to ONLY include the minutes within the desired time range.

If the start time is earlier than the range start time, the range start time is substituted in, else the original start remains.
If the end time is later than the range end time, the range end time is substituted in, else the original end remains.

The remaining values in the newly constituted table are summed.


If you change your range of rows down to say only 5 rows, all through the formula, then you would be able to sue the FORMULAS tab > Evaluate Formula to watch this baby unfold before your eyes. Very fun to watch.

[dflak]

I had to attach this as a text file because the board thinks my greater than and less than are HTML code tags.

[shg]

the board thinks my greater than and less than are HTML code tags

Add a space after them.

This is apparently an array formula

I'll try to take it in pieces. Wherever I see a range using J I substituted startpair, whenever I see a range using K I substituted endpair - this is what it would look like if you were using Excel Tables.

I exploded out the formula to explain it better

=SUM(
IF(
(C3 > startpair)*(B3 < endpair)*(A3 = employeenumber),
(
(IF(C3 < endpair, C3, endpair) - IF(startpair > B3, startpair, B3) ) *24 * 60)
,0)
)

[iceandfire]

Whoa!
Thanks!
Might be a noob question but what does the * do in the first IF?

[dflak]

The * works sort of like a logical AND statement in an array formula.

This article explains the concept behind array formulas.

[iceandfire]

I'm sorry. But I don't see the article and I'd really want to read it. Specially since Google hasn't been helpful (or my key/searchwords suck).

[dflak]

I'll try attaching again: http://www.utteraccess.com/wiki/inde...Array_Formulas.