1

Imagine you're creating a Google Sheet to track all of your tasks, how long they take, and their deadlines:

Task                    |Number of days| Start Date | Date Done
Buy plane tickets       |       5      | 15.12.2019 | 20.12.2019
Reserve hotel           |       2      | 21.12.2019 | 23.12.2019
Prepare snacks for trip |       1      | 24.12.2019 | 25.12.2019
Go on trip              |      14      | 26.12.2019 | 10.01.2020

Is there any way in Google Sheets to update the dates dynamically if another task is added in the middle?

Say you wanted to add renting a car to the plan, and it will take you five days. Is there any way in google sheets that once you add it, the sheet would automatically update from this:

Task                    |Number of days| Start Date | Date Done
Buy plane tickets       |       5      | 15.12.2019 | 20.12.2019
Reserve hotel           |       2      | 21.12.2019 | 23.12.2019
Rent a car              |       5      | 24.12.2019 | 29.12.2019
Prepare snacks for trip |       1      | 24.12.2019 | 25.12.2019
Go on trip              |      14      | 26.12.2019 | 10.01.2020

To this?

Task                    |Number of days| Start Date | Date Done
Buy plane tickets       |       5      | 15.12.2019 | 20.12.2019
Reserve hotel           |       2      | 21.12.2019 | 23.12.2019
Rent a car              |       5      | 24.12.2019 | 29.12.2019
Prepare snacks for trip |       1      | **30.12.2019** | **31.12.2019**
Go on trip              |      14      | **01.01.2020** | **14.01.2020**

Essentially automatically delay all the other tasks based on the newly introduced task?

Link to Google Sheet with an example of desired behaviour

If there is no way in Google Sheets, is there any other tool you know to do this?

  • what's the difference? - i.stack.imgur.com/8bmmK.png share a copy of your sheet with example of desired output – user0 Jul 24 at 18:46
  • The difference is marked with ** - also, how do I share a sheet? – Mike Meyers Jul 25 at 7:12
  • just paste sheet url/link into comment or your post and enable sharing in your sheet (top right corner - green share button) – user0 Jul 25 at 7:16
  • Done - added the Google Sheet link – Mike Meyers Jul 25 at 7:28
1

paste in C4 cell:

=ARRAYFORMULA(QUERY(IF(B4:B=""; ;SUMIF(ROW(B3:B); "<="&ROW(B3:B); B3:B)+
 DATEVALUE(C3)); "where Col1 is not NULL"; 0)+ROW(INDIRECT("A1:A"&COUNTA(A4:A))))

0


paste in D3 cell:

=ARRAYFORMULA(IF(LEN(A3:A); TO_DATE(C3:C+B3:B); ))

0

spreadsheet demo

  • 1
    This is perfect and exactly what I was looking for - thanks @user()! – Mike Meyers Aug 2 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.