Create flatten dates sequence in Google Sheets in one column (without using MACRO FUNCTIONS)
I have a table in Google Sheets with the following structure:
| Start Date | End Date |
|---|---|
| Start Date 1 | End Date 1 |
| Start Date 2 | End Date 2 |
| Start Date 3 | End Date 3 |
I'm using the following formula:
IFERROR(FLATTEN( SEQUENCE(DATEDIF(D2,E2,"D"), 1, D2, 1), SEQUENCE(DATEDIF(D3,E3,"D"), 1, D3, 1), SEQUENCE(DATEDIF(D4,E4,"D"), 1, D4, 1), SEQUENCE(DATEDIF(D5,E5,"D"), 1, D5, 1) ),"")
This formula is working fine for what I want as a result (all the dates between start and end date in one single column).
However, it's not very efficient in the sense that for every row that is added to the table I need to add another SEQUENCE function to my formula.
Is there a way to do this automation with only one SEQUENCE function?
The idea would be something like this:
IFERROR(FLATTEN( SEQUENCE(DATEDIF(D2:D,E2:E,"D"), 1, D2:D, 1)),"")
But it just doesn't work. I was wondering if I could do this without requesting any MACROS also.
I tried with the following: SEQUENCE( SUM( DATEDIF(D2:D,E2:E,"D") ) , 1 ,SEQUENCE(DATEDIF(D2:D,E2:E,"D"), 1, D2:D, 1),1)
and also with ArrayFormula(iferror(if(A2:A="",,transpose(split(concatenate(rept(A2:A&",",1+networkdays(A2:A,B2:B))),",")))))
I was expecting to have a sequence of all the dates between the start date and the end date in one single column, in a way that after the end date, the next row would have the following start date, and so on.
Thank you in advance!
You may try:
=query(reduce(,sequence(counta(A2:A)),lambda(a,c,{a;SEQUENCE(index(map(A2:A,B2:B,lambda(x,y,if(x="",,days(y,x)+1))),c),1,index(A2:A,c),1)})),"offset 1",0)
Formatthe output column to needed date styling
- Smallest number that cannot be formed from sum of numbers from array
- Group 2d array data by one column and accumulate another column's values in subarrays
- Reverse the order of array elements
- How to log JavaScript objects and arrays in winston as console.log does?
- Overwrite associative elements of one array with another associative array and reverse the array order
- Convert a flat array of delimited values into a hierarchical array
- How to reindex an associative array?
- Set keys of a flat indexed array using the array's values
- Convert two columns in an array of objects into an associative array
- Combine columnar values in each row of a 2d array to form dynamically keyed elements
- Where can I fill an array in Angular?
- Validating an array's length against another array's length using joi
- How can I use the wildcard function in a Word VBA macro with an array?
- How to parse a querystring formatted string?
- Parse a string resembling PHP array syntax into an associative array
- Why does giving a foreach a 3-element array perform 3 iterations?
- Data Class Either Object or Array
- How to Allocate memory from a new virtual page in C?
- Group values of an associative array and accumulate keys as comma-separated values
- Pass comma separated key string and get value of array according to key in PHP
- AssertEquals 2 Lists ignore order
- Finding out how many times an array element appears
- Remove the time substring from an array of datetime strings
- Group associative elements by keys with contiguous dates to create an array of associative arrays
- Parse a string with 3 delimiters and return the keys which contain a qualifying value
- Swift: Pass array by reference?
- PHP append one array to another (not array_push or +)
- Initializing a dynamically-sized Variable-Length Array (VLA) to 0
- Splitting array into groups using typescript/javascript
- Array.Join in .Net?