Google-sheets – ny way to dynamically modify dates based on number of days something would take

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-query

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?

Best Answer

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))))

paste in D3 cell:

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

spreadsheet demo

Related Solutions

Google-sheets – How to delete a row in a Google Sheet based on date

Directly accessing date cells in a spreadsheet from a script is kind of painful. I would add a helper column, for example C2 =value(A2+B2) if the columns A and B contain StartDate and Duration. This makes the entry in C an integer. Then a script can delete rows based on this value. Like this

function cleanup() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var values = sheet.getDataRange().getValues();
  var today = Date.now()/86400000 + 25569;
  for (var i = values.length - 1; i >= 0;  i--) {
    if (values[i][2] - today > 1) {
      sheet.deleteRow(i+1);
    }
  }  
}

Explanation: Date.now()/86400000 + 25569 is conversion from JavaScript timestamp to spreadsheet format. The loop traverses the sheet from bottom up, so that deletion of rows does not mess with enumeration of remaining rows. The comparison values[i][2] - today > 1 says "value in column C" is greater than today + 1, where +1 is a cushion meant to account for time zone discrepancies. (I presume you can afford to keep an expired task in the spreadsheet for a few hours.) If the column was E, you'd need values[i][4] there: enumeration begins with 0 in JavaScript.

One you have this function there, set a trigger (under Resources) to run it daily, or hourly if you prefer.

Google-sheets – Tally duration, client, client category in each Google sheets Timesheet

I'll outline the strategy in three steps. Some of them may be combined, but doing things step by step makes the process more manageable.

Import ranges

Start by pulling the data from assorted spreadsheets into one summary spreadsheet. For example, create a separate sheet/tab for each source, enter =importrange(..., "A:E") in A1, and something like

=arrayformula(if(A:A <> "", {"This_Client", "This_Category"}, ))

to mark the client and category for the source.

Combine data

In a summary sheet, enter

={filter(Sheet1!A:G, len(Sheet1!A:A)); filter(Sheet2!A:G, len(Sheet2!A:A)) }

to combine all the sources, excluding empty rows.

Query data

Use a query to summarize data and present selected columns.

=query(Summary!A:G, "select A, sum(B), F, G group by A, F, G")