CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Cookie Monster
Cookie Monster

Reputation: 11

Query data based on time interval/frequency

I'm trying to have a calendar display a series of activities based on their type, time and frequency for an easier visualization of data.

So far, I have managed to create a formula that correctly fetches the data that I have on a repository and displays it on the calendar. However, I'm not sure how I can have it account for entries that have a frequency (happening every x days).

For an easier understanding here are screenshots of both the table and the schedule table

schedule

And here's the current formula I'm using to display the event/activity title in each day/hour at C12 for example:

=IFERROR(
    INDEX(Repository!$K:$K, 
        MATCH(
            C$10, 
            IF(
                (Repository!$G:$G=$G$8)*
                (Repository!$H:$H=$K$8)*
                (Repository!$N:$N>=$B12)*
                (Repository!$N:$N<$B12+TIME(2,0,0)),
               Repository!$D:$D),
            0)
         ),
      "")

What I'm currently missing on the formula is a way to correctly account for the start/end date as well as frequency and understand if each day falls under the specified criteria. In case the frequency is 0 then I'd like to have it discard the end date at all (in case for some reason I end up forgetting to set the end date).

I have tried to work with the formula provided to account for the frequency but nothing that I tried seemed to work.

Minimal example requested by @GabrielCarballo

Entry on the table with a 2 days frequency: entry table

Expected result on the schedule: entry schedule

So basically, the formula on each cell should check for the start date, end date and frequency of the activity and identify if the specific date on the schedule falls under the specified timeframe.

In this minimal example, the activity starts on the 7th December and repeats every 2 days until the 14th of December.

Upvotes: 0

Views: 126

Answers (1)

player0
player0

Reputation: 1

use in C6:

=INDEX(IFNA(VLOOKUP(TEXT(C4:P4+B6:B14, "e-m-d-h-m")&G$2&K$2, SPLIT(FLATTEN(MAP(
 Repository!D$4:D, Repository!O$4:O, Repository!P$4:P, Repository!N$4:N, 
 Repository!G$4:G, Repository!H$4:H, Repository!K$4:K, LAMBDA(d, o, p, n, g, h, k, 
 IF(DAYS(o, d)>=SEQUENCE(1, MAX(DAYS(o, d)), 0, p), TEXT(d+SEQUENCE(1, 
 MAX(DAYS(o, d)), 0, p)+IF(ISODD(HOUR(n)), (HOUR(n)*"1:00")-"1:00", HOUR(n)*"1:00"), 
 "e-m-d-h-m")&g&h&"×"&k, )))), "×"), 2, )))

Upvotes: 0

Related Questions