Search code examples
dategoogle-sheetsgoogle-sheets-formula

Google Sheets - Expanding dataset according to months between 2 dates

I would like to be able to expand a dataset to include one row per "person" per month, filtered by Start and End Date.

Consider the example below:

enter image description here

This is the desired outcome

enter image description here

Inputs are a start and end date, which could be anywhere between the start of a given financial year (01/04/20XX) and the end of that financial year (31/03/20XY). The output rows contain each month comprised between these 2 dates for which there is a matching entry in the input (see example output)

I have an example sheet here.

Any help would be appreciated, thanks

Solution

  • Try:

    =ARRAYFORMULA(SPLIT(UNIQUE(QUERY(FLATTEN(IF(DAYS(C2:C4,B2:B4)>=SEQUENCE(1,1000,0),A2:A4&","&TEXT(B2:B4+SEQUENCE(1,1000,0),"YYYY-MMM")&","&D2:D4,"")),"where Col1 is not null")),","))

    Result: enter image description here

    This works however the only limitation is you have to manually edit the formula to specifically define the End range. It crashes when I set it to use the whole column using the formula below:

    =ARRAYFORMULA(SPLIT(UNIQUE(QUERY(FLATTEN(IF(DAYS(C2:C,B2:B)>=SEQUENCE(1,1000,0),A2:A&","&TEXT(B2:B+SEQUENCE(1,1000,0),"YYYY-MMM")&","&D2:D,"")),"where Col1 is not null")),","))

    I am assuming this is because of the Query function which runs a Google Visualization API Query Language query across data making it very long and heavy processing.

    Explanation:

    To explain how this works using the formula from this link: Google sheets - List of dates between a list of start and end dates to get the dates between, but I changed the IF to return all combined 4 columns. Converted it to date format using TEXT (YYYY-MMM). I then used the UNIQUE to remove duplicates so it will only show the months. Then make use of SPLIT to split across 3 columns.

    References:

    Hope this helps!