I’m working on a Google Sheets problem where I need to expand date ranges into individual dates while keeping the corresponding ID values attached to each date.
Basically, I have a table with three columns:
- Column A: ID numbers
- Column B: Start dates
- Column C: End dates
What I want is to create a flattened list where each date between the start and end dates gets its own row, and each row shows the ID that corresponds to that date range.
For example, if I have ID “ABC123” with start date 2024-01-01 and end date 2024-01-03, I want the output to show:
- ABC123 | 2024-01-01
- ABC123 | 2024-01-02
- ABC123 | 2024-01-03
I found a formula that can generate the date sequences, but I can’t figure out how to make it also repeat the ID values for each corresponding date. The formula works fine for creating the date list, but I’m stuck on how to modify it to include the ID column data.
Is there a way to do this with standard Google Sheets functions without using any custom scripts or macros?
you can use a combo of SEQUENCE and SPLIT! use =ARRAYFORMULA(SPLIT(FLATTEN(REPT(A2:A&“|”, C2:C-B2:B+1)&SEQUENCE(C2:C-B2:B+1,1,B2:B)),“|”)) to generate that list. it works like a charm!
Had this exact problem 6 months ago tracking project timelines. Here’s what worked for me: =ARRAYFORMULA(TRANSPOSE(SPLIT(TEXTJOIN(“,”,TRUE,FLATTEN(A2:A&“|”&TEXT(SEQUENCE(1,MAX(C2:C-B2:B+1),0)+TRANSPOSE(B2:B),“yyyy-mm-dd”))),“|”))) Put this in two columns next to each other - it’ll give you the ID and date pairs. The trick is using TEXTJOIN to mash everything into one string, then split it back out. Just make sure your date columns are formatted as dates first or you’ll get wonky results.
FLATTEN + FILTER handles this perfectly. Use this formula: =ARRAYFORMULA(FILTER({FLATTEN(REPT(A2:A,C2:C-B2:B+1));FLATTEN(SEQUENCE(ROWS(A2:A),1,B2:B,1))},FLATTEN(SEQUENCE(ROWS(A2:A),1,B2:B,1))<=FLATTEN(REPT(C2:C,C2:C-B2:B+1)))). It creates both columns at once by filtering the expanded sequences based on your end dates. I’ve used this for inventory tracking with products that had different availability windows. The best part? It automatically adjusts for varying date ranges without manual tweaks. Just make sure your date columns are formatted as dates first.