Forum Discussion
Returning a value from a discontinuous range (a formatted calendar with data) for a specific date
For the sake of the rest of us (well, maybe just me), Sergei, could you explain those two formulas, and how they're working, what they do? I don't know how you come up with them for something like this.
The core of the formula is
INDEX(MonthRange,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1)which returns all dates in the month as 1D array.
Let say we have such range and would like to return all values in yellow sequentially:
The rest is just arithmetic. Finding relative position of the value in such array (e.g. 5 for a15) and knowing number of cells in each row and step between row with MOD and INT we find position of this value in source range (5th row, second column). INDEX(range,5,2+1):INDEX(range,5,2+1+3) returns values for 3 cells under found one. Filter result if necessary.
All above steps much easier to code with LET(). If not available, with named ranges and formulas. Or straightforward code as in second formula.