Adding in multiple durations to Start Time formula

Jemma Stroud

Hello! I originally created my plan using to have start date then to add in the start and end times. I was using this formula: =StartDate@row + duration@row + "". However, I just realized that my project will be operating under two sets of working times: 8 hours to start then pivoting to 24 hour days. Since smartsheet only allows for one set of working times, I need to adjust the start times for the first set of tasks so they start at 8 am instead of midnight. I tried adjusting the formula to the following: =StartDate@row + duration@row + adjustment@row but what happens is the following: 12/3/21 8:00 AM8h. It's not adjusting the time, just adding the text in the Adjustment field.

Is there a way to adjust the formula so that it will add in both the duration and adjustment? If not, is there an alternative method that I could try? I asked a colleague and their only suggestion was to reset all my dependencies to the Adjustment field but that seems too time consuming since my plan is 4k+ lines.

Any help would be greatly appreciated!

Julio S.

Hi @Jemma Stroud,

Have you tried converting the relevant duration to hours instead of using the adjustment column? this should allow you to reflect the target times in a more precise way just by using the original formula that you were using (=StartDate@row + duration@row + "").

For example, to obtain 12/3/21 04:00PM in your target cell, based on what it looks you'd like to achieve, if your start date is 12/1/21, your project length of the day is 24h and you have a duration of 2 days (2d), your formula would return 12/3/21 12:00 AM. However, if you switch the duration to 40h instead, the target cell will reflect 12/3/21 4:00 PM. If you have set the project length of your day to 8h, 18.67h should be the duration that gives you 12/3/21 4:00 PM on the target cell.

Alternatively, you may also want to consider using elapsed time or other duration adjustments in your project. E.g e64h should give you the same result based on the above example of 24h length of days.

I would also recommend consulting this post from @Paul Newcome if you need more ideas on how to calculate time in different ways by using formulas.

If this wouldn't be of help or you'd need further advise, please include some screenshots of your sheet taking good care to hide any confidential information that needs not be shared.

I hope this can be of help and wish you happy festive days.

Cheers!

Julio