Hello, I noticed when the duration column for GANTT view is assigned, it does not automatically calculate the duration for the task so I created another column to calculate the duration using a formula;
rounddown(n-m,0)
This gets me the correct number of days to display for the calculated duration.
Then I created an automation that when this column is updated, the GANTT duration column is changed to this number. The automation works except for one issue I do not know how to fix. The GANTT column is displaying numbers 1 higher than my calculated duration it is supposed to mirror. To counter this I tried the formula;
rounddown(n-m,0)-1
I thought since it is displaying one higher than the value I calculate, subtracting 1 should fix it, but the result was my calculated duration changed as expected but the GANTT duration did not and was now displaying 2 higher. It only seems to take the duration between the two dates and include the start date as a day but I really want it to not include the start day. So if a task is due tomorrow, I want it to say 1 day for duration instead of 2. Is this a bug? Or is the GANTT duration column meant to only count the days between the dates including the start date? And if that is the case, why didn’t it automatically populate without me copying values from another column?
1 Like
It’s pretty standard for Gantt charts to consider the task as starting at the beginning of the Start day, and ending at the end of the End Date. This means that a task with Start Date = End Date is a 1 day duration. This carries forward with the way predecessors/dependencies typically work … meaning that the successor task’s start date is defined as the next work day following the predecessor’s End Date.
You may be able to force a different meaning, but unless you are working with datetime values (which SSDC does allow), I’d recommend sticking with these general definitions.
I’ve also experimented with using automations to modify the PM columns. Problem I found is that the change of the date via a formula doesn’t necessarily trigger the automation. That may be a bug or a limitation, but for this particular application it may not matter if future updates allow us to use formulas in PM columns.
1 Like
Yes, I can understand that. I’m using this for my field engineers and it makes more sense to not include the first day to avoid confusion so I was hoping but not a big deal. I have found another odd thing that is happening in my spreadsheet. It might be a bug. I used the project management tool for GANTT views and changed the duration column to something other than what it was originally assigned and then changed it back and it started auto-updating which it wasn’t before. The odd thing though is that it was auto-updating different durations for the same start and end date.
I am not sure if I can replicate this but to help you guys see the same thing; I used a form to fill in row data for start and end dates. At first the duration column would not auto populate so I made a new duration column with a formula to calculate it and ran an automation to populate the GANTT duration column shown in the image. With that still not working I deleted the automation and formula and changed the dedicated duration column for GANTT view. Then I immediately changed it back and the GANTT column began auto-populating durations which it was not doing before. That is where I saw that the auto-populating was not consistent as shown in the image.
Let me know what you think or if you need more information.
UPDATE: When adding a row from a form entry, GANTT duration column will not auto-populate. It remains blank. It will auto-populate after a change is made to a date in the row but it is not auto-populating accurately.
Moving a row to another sheet and back also deletes the duration, even if the other sheet columns are formatted the same.
1 Like
I deleted the duration columns and remade them and it fixed itself so it must of been just a small bug. Adding a row through a form though does not get a duration until an edit is made. I am going to place this in the suggestion thread.
2 Likes
