Are you tired of manually expanding dates and assigning values in Google Sheets? Well, fret no more! In this article, I’m going to spill the beans on how to effortlessly utilize the power of SEQUENCE and VLOOKUP functions to achieve this task with ease.
The Old Method
Before we dive into the step-by-step instructions, let’s take a moment to understand the limitations of the old-school method that was used before the introduction of the LAMBDA function in Google Sheets.
Firstly, the start date of a task must always be greater than the end date of the previous task. This condition applies to all tasks in the project. Additionally, the “Description” column must not be left blank.
In situations where tasks fall within the same period or overlap, the only option was to display them as comma-separated values. For example, if you had two tasks from 02/03/2020 to 05/03/2020, you would represent them as:
| Start Date | End Date | Description |
|---|---|---|
| 02/03/2020 | 05/03/2020 | Task 1, Task 2 |
If these workarounds don’t meet your requirements, you might resort to using helper tabs. But worry not, because there’s a better way!
Steps to Expand Dates and Assign Values
Now, let’s get into the nitty-gritty of expanding dates and assigning values in Google Sheets. Here’s a step-by-step guide:
1. Find Min and Max Dates
To begin, find the smallest and largest dates in the range. In cell G2, enter the formula =MIN(A2:B). In cell H2, enter the formula =MAX(A2:B).
2. Expand the Min and Max Dates
Next, generate a list of sequential dates between the minimum and maximum dates. In cell I2, enter the formula =SEQUENCE(DAYS(H2, G2)+1, 1, G2). Then, select the range I2:I and apply the Format > Number > Date.
3. Helper Table from Source Data
Now, flatten the data in columns A2:B4 into columns K2:K and copy-paste the corresponding tasks to L2:L. Insert one row after each task and enter the dates that should be “end_date_above + 1”.
4. Vertical Lookup
In cell J2, insert the following VLOOKUP formula: =ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1)). With this step, we have successfully expanded dates and assigned values in Google Sheets.
5. Combine Steps 1, 2, and 4 Formulas
To simplify the process, we can combine the formulas from Steps 1, 2, and 4. Follow these steps:
-
First, combine the formula from Step 2 (I2) and Step 4 (J2) in cell M2:
={SEQUENCE(DAYS(H2, G2)+1, 1, G2), ArrayFormula(VLOOKUP(I2:I, K2:L, 2, 1))}. Then, edit the formula and replace the cell references H2 and G2 with the formulas from the corresponding cells. -
Next, replace I2:I with the I2 formula:
={SEQUENCE(DAYS(MAX(A2:B), MIN(A2:B))+1, 1, MIN(A2:B)), ArrayFormula(VLOOKUP(SEQUENCE(DAYS(H2, G2)+1, 1, G2), K2:L, 2, 1))}. Finally, edit the formula again to replace the remaining cell references H2 and G2 with the corresponding formulas. -
Don’t forget to delete values in columns G to J, while keeping the helper table (K2:L) as it is.
The New LAMBDA Formula
If you’re looking for a more efficient method, I highly recommend using the new LAMBDA formula in Google Sheets. Here’s how you can implement it:
Please refer to image #1 for the sample data in cells A1:C. In cell E2, enter the following LAMBDA-based formula after clearing E2:F: =ArrayFormula(SPLIT(TOCOL(MAP(A2:A, B2:B, C2:C, LAMBDA(a, b, c, IF(a*b, SEQUENCE(1, b-a+1, a)&"|"&c,)))), 1). Select E2:E and apply the Format > Number > Date.
For a detailed example, please refer to the ‘Lambda Eg 1’ tab in my sample Sheet.
And voila! With these methods, you can easily expand dates and assign values in Google Sheets without breaking a sweat.
Remember, if you need any further assistance, don’t hesitate to visit Crawlan.com for more helpful tutorials and resources.
Happy Spreadsheeting, besties!
