Calculate new start time for whole schedule

Sascha Nachtnebel ✭

10/13/20 edited 10/13/20 inFormulas and Functions

Hi there!

As an event organizer, I want to automatically update my schedule in smartsheet when a session takes longer than expected.

Column 1: Start Time (HH:MM)

Column 2: End Time (HH:MM)

Column 3: duration in minutes

Column 4: new start time (HH:MM)

how can I arrange a formula so that once entering a new start time in Column 4, the rest of the schedule changes? Basically, I need the exact functionality that is already there with start date, end date, duration and predecessor.

Thanks in advance!

Sascha

Bildschirmfoto 2020-10-13 um 10.48.14.png

Tags:

0 ·

Answers

Ramzi K ✭✭✭✭✭

10/13/20

@Sascha Nachtnebel

If this is a project sheet (i.e. using Smartsheet's project settings and functionality), you will not be able to put formulas in the duration or end date columns. You will need to make your changes manually.

Cheers,

Ramzi

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me:[email protected]

If this post helped you out, please help the Community bymarking it as the accepted answer/helpful.

0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

Learn more

=MAX(MIN((TODAY() - Start@row) \/ (Finish@row - Start@row), 1), 0)<\/p>

<\/p>

This does the percentage:<\/p>

(TODAY() - Start@row) \/ (Finish@row - Start@row)<\/p>

<\/p>

This caps it at 100%:<\/p>

MIN(..............., 1)<\/p>

<\/p>

This keeps it from going negative for future tasks:<\/p>

MAX(..............., 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106871,"type":"question","name":"Parent Status based on Children Harvy ball status.","excerpt":"Dear team, I am trying to build a status update tracker, for teams to update each milestone. They are expected to update each milestone using the harvey ball status (0% \/25% \/50 % \/75% \/100%). I am trying to automate the status of parent based on children's Harvey ball status as below with logic as \" If the status of all…","categoryID":322,"dateInserted":"2023-06-25T03:09:21+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162738,"lastUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3375410937,"url":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106871\/parent-status-based-on-children-harvy-ball-status","format":"Rich","lastPost":{"discussionID":106871,"commentID":382196,"name":"Re: Parent Status based on Children Harvy ball status.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382196#Comment_382196","dateInserted":"2023-06-26T02:39:36+00:00","insertUserID":162738,"insertUser":{"userID":162738,"name":"ChaitanyaK","url":"https:\/\/community.smartsheet.com\/profile\/ChaitanyaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!7z8le8P6Q_o!5NnvuQ5DbTI!qYVEcd1EfTU","dateLastActive":"2023-06-26T04:03:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/81O3LR1GWG7D\/picture1.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Picture1.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T02:28:56+00:00","dateAnswered":"2023-06-25T04:29:41+00:00","acceptedAnswers":[{"commentID":382168,"body":"

With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:<\/p>

=IF(COUNTIF(CHILDREN([BallCell]@row), \"Empty\") = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), \"Full\") = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>

If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:<\/p>

=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), \"Not Started\", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), \"Completed\", \"In Progress\"))<\/p>

In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106869,"type":"question","name":"Formula to Assign Symbol based on a cell's value","excerpt":"This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis. What I am trying to do: (Using the pain scale symbols) Have symbols automatically changed based on the value in the corresponding cell. I am already using a formula to assign a value based on…","categoryID":322,"dateInserted":"2023-06-24T20:02:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-25T22:29:54+00:00","insertUserID":158092,"insertUser":{"userID":158092,"name":"MeganJF","url":"https:\/\/community.smartsheet.com\/profile\/MeganJF","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DgFow9e-1vo!YfMmmJOUTjo!UDpVBcB6ikY","dateLastActive":"2023-06-25T17:43:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":139601,"lastUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":31,"score":null,"hot":3375372134,"url":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106869\/formula-to-assign-symbol-based-on-a-cells-value","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106869,"commentID":382193,"name":"Re: Formula to Assign Symbol based on a cell's value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382193#Comment_382193","dateInserted":"2023-06-25T22:29:54+00:00","insertUserID":139601,"insertUser":{"userID":139601,"name":"jmyzk_cloudsmart_jp","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmyzk_cloudsmart_jp","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZBVD3K8PY0D5\/n7CZ1F4XWEM9Y.JPG","dateLastActive":"2023-06-26T04:50:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-25T17:10:13+00:00","dateAnswered":"2023-06-25T03:32:30+00:00","acceptedAnswers":[{"commentID":382165,"body":"

Hi @MeganJF<\/p>

I guess you forget to use AND( ).<\/p>

For example, <\/p>

IF([Total Value to User Score]@row = <3.8, >4.7, \"Mild,<\/li><\/ul>
should be<\/p>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
  However, the following would be more straightforward.<\/p>
  =IF([Total Value to User Score]@row >= 4.8, \"No Pain\", <\/li>
  IF([Total Value to User Score]@row >= 3.8, \"Mild\", <\/li>
  IF([Total Value to User Score]@row >= 2.8, \"Moderate\", <\/li>
  IF([Total Value to User Score]@row >= 1.8, \"Very Severe\", <\/li>
  IF([Total Value to User Score]@row < 1.8, \"Extreme\", \"//www.santa-greenland.com/community/discussion/72161/\")))))<\/li><\/ul>
  \n
  \n \n $\"Formula$ <\/img><\/a>\n <\/div>\n<\/div>\n
  
  <\/p>
  Please take a look at the published sheet, as well.<\/p>
  \n \n https:\/\/app.smartsheet.com\/b\/publish?EQBCT=ef585943fa254224991d3e1a0762da5b\n <\/a>\n<\/div>
  
  <\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">