Formula to calculate End Date and Time based on Start Date, Time and Task duration

2

Answers

  • Shanelle
    Shanelle ✭✭

    @Paul NewcomeThank you so much!! It works! I did have the wrong column type for the completion date.

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    We finally got it working! That's great! Happy to help.️


    And don't feel bad about the column type. I do it to myself all the time. Hahaha.

    10xViz.com

    · Share on Facebook Share on Twitter
  • Lyn Pringle
    Lyn Pringle ✭✭✭

    @Paul NewcomePlease can you help me. :)

    I have made use of the 24hr formula to calculate the number of hours worked - which works like a bomb, thank you - however not able to figure out how to create a running total.

    Do you have a special formula to autosum the number of hours worked.

    My sheet looks like this

    Annotation 2020-05-06 140848.png


    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Lyn PringleIf you are using the solution above, you are going to want to reference the Duration Column. That houses the numerical values that can be used in math based calculations.

    10xViz.com

    · Share on Facebook Share on Twitter
  • Zevy L
    Zevy L ✭

    @Paul Newcome, can you help me with a similar question?

    If I separate columns, one for date and one for time, is there a way to set a reminder let's say 24 hours after the time indicated across all columns? So for instance, I have row #1 entered as June 10, 10:00am and row #2 entered as June 20, 11:00am. Can I set a a 24 reminder so that row #1 will remind me at and row #2 will remind me on June 21, 11:00am?

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Zevy LIf I remember correctly, there is actually a thread where this exact thing was discussed. Basically what you will need to do is convert the time into a number and then set up 24 automations (one for each hour) with the trigger being date based and the condition being the time.

    You can use this to pull the hour number and have it converted to a 24 hour base so that you do not need to build additional criteria for AM and PM into your Automation:

    =VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

    10xViz.com

    · Share on Facebook Share on Twitter
  • Zevy L
    Zevy L ✭

    Thank you, Paul! How do I "pull the hour number and have it converted to a 24 hour base"?

    · Share on Facebook Share on Twitter
  • Zevy L
    Zevy L ✭

    保罗,我只是试着根据你设置instructions. If the time is set for say 1pm, will I get the reminder at 1pm? The automation doesn't seem that way... (see screenshot attached

    Capture.JPG

    ).

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No. You would need to change the time at the bottom of the Trigger portion. You would also need to change the trigger to run off of a date field and select the column that contains the dates that the reminders would be sent.

    10xViz.com

    · Share on Facebook Share on Twitter
  • Mindfull
    Mindfull ✭✭
    The formulas I have provided are copy\/pasted from my sheet based on the columns in my above screenshot.<\/p>

    The error in your first screenshot (Form 4) should be because of your column type. Make sure the [Completion Date] column is a date type column.<\/p>

    Make sure your column names and types match mine exactly, and you should be able to copy\/paste. Once you have the formulas in place, you can change your column names and the formulas will automatically update. Here is a list of the columns<\/strong> from my above screenshot in order from left to right, their column type<\/em>, and their formulas\/input:<\/p>

    [Actual Clean Start Date]<\/strong>:<\/p>

    date<\/em><\/p>

    Manual Entry<\/p>

    [Actual Clean Start Time]<\/strong>:<\/p>

    text\/number<\/em><\/p>

    Manual Entry<\/p>

    [Standard Clean Routing (Hrs)]<\/strong>:<\/p>

    text\/number<\/em><\/p>

    Manual Entry<\/p>

    Duration<\/strong>:<\/p>

    text\/number<\/em><\/p>

    =(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) \/ 60) + [Standard Clean Routing (Hrs)]@row<\/p>

    [Completion Date]<\/strong>:<\/p>

    date<\/em><\/p>

    =[Actual Clean Start Date]@row + INT(INT(Duration@row) \/ 24)<\/p>

    [Completion Time]<\/strong>:<\/p>

    text\/number<\/em><\/p>

    =MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) \/ 60) + [Standard Clean Routing (Hrs)]@row), 24) + IF(MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) \/ 60) + [Standard Clean Routing (Hrs)]@row), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) \/ 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) \/ 60) + [Standard Clean Routing (Hrs)]@row)) * 60 < 10, "0") + ((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) \/ 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Actual Clean Start Time]@row), 12), IF(CONTAINS("a", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(":", [Actual Clean Start Time]@row) + 1, 2)) \/ 60) + [Standard Clean Routing (Hrs)]@row)) * 60 + IF(MOD(INT(Duration@row), 24) >= 12, "pm", "am")<\/p>

    [Expected Clean Completion Date]<\/strong>:<\/p>

    text\/number<\/em><\/p>

    =[Completion Date]@row + " " + [Completion Time]@row<\/p>","bodyRaw":"[{\"insert\":\"The formulas I have provided are copy\\\/pasted from my sheet based on the columns in my above screenshot.\\nThe error in your first screenshot (Form 4) should be because of your column type. Make sure the [Completion Date] column is a date type column.\\n\\nMake sure your column names and types match mine exactly, and you should be able to copy\\\/paste. Once you have the formulas in place, you can change your column names and the formulas will automatically update. Here is a list of the \"},{\"attributes\":{\"bold\":true},\"insert\":\"columns\"},{\"insert\":\" from my above screenshot in order from left to right, \"},{\"attributes\":{\"italic\":true},\"insert\":\"their column type\"},{\"insert\":\", and their formulas\\\/input:\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"[Actual Clean Start Date]\"},{\"insert\":\":\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"date\"},{\"insert\":\"\\nManual Entry\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"[Actual Clean Start Time]\"},{\"insert\":\":\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"text\\\/number\"},{\"insert\":\"\\nManual Entry\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"[Standard Clean Routing (Hrs)]\"},{\"insert\":\":\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"text\\\/number\"},{\"insert\":\"\\nManual Entry\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"Duration\"},{\"insert\":\":\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"text\\\/number\"},{\"insert\":\"\\n=(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS(\\\"p\\\", [Actual Clean Start Time]@row), 12), IF(CONTAINS(\\\"a\\\", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) + 1, 2)) \\\/ 60) + [Standard Clean Routing (Hrs)]@row\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"[Completion Date]\"},{\"insert\":\":\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"date\"},{\"insert\":\"\\n=[Actual Clean Start Date]@row + INT(INT(Duration@row) \\\/ 24)\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"[Completion Time]\"},{\"insert\":\":\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"text\\\/number\"},{\"insert\":\"\\n=MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS(\\\"p\\\", [Actual Clean Start Time]@row), 12), IF(CONTAINS(\\\"a\\\", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) + 1, 2)) \\\/ 60) + [Standard Clean Routing (Hrs)]@row), 24) + IF(MOD(INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS(\\\"p\\\", [Actual Clean Start Time]@row), 12), IF(CONTAINS(\\\"a\\\", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) + 1, 2)) \\\/ 60) + [Standard Clean Routing (Hrs)]@row), 24) <> 12, IF(CONTAINS(\\\"p\\\", IF(MOD(INT(Duration@row), 24) >= 12, \\\"pm\\\", \\\"am\\\")), -12), IF(CONTAINS(\\\"a\\\", IF(MOD(INT(Duration@row), 24) >= 12, \\\"pm\\\", \\\"am\\\")), 12)) + \\\":\\\" + IF(((VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS(\\\"p\\\", [Actual Clean Start Time]@row), 12), IF(CONTAINS(\\\"a\\\", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) + 1, 2)) \\\/ 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS(\\\"p\\\", [Actual Clean Start Time]@row), 12), IF(CONTAINS(\\\"a\\\", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) + 1, 2)) \\\/ 60) + [Standard Clean Routing (Hrs)]@row)) * 60 < 10, \\\"0\\\") + ((VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS(\\\"p\\\", [Actual Clean Start Time]@row), 12), IF(CONTAINS(\\\"a\\\", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) + 1, 2)) \\\/ 60) + [Standard Clean Routing (Hrs)]@row - INT((VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) + IF(VALUE(LEFT([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) - 1)) <> 12, IF(CONTAINS(\\\"p\\\", [Actual Clean Start Time]@row), 12), IF(CONTAINS(\\\"a\\\", [Actual Clean Start Time]@row), -12)) + VALUE(MID([Actual Clean Start Time]@row, FIND(\\\":\\\", [Actual Clean Start Time]@row) + 1, 2)) \\\/ 60) + [Standard Clean Routing (Hrs)]@row)) * 60 + IF(MOD(INT(Duration@row), 24) >= 12, \\\"pm\\\", \\\"am\\\")\\n\\n\"},{\"attributes\":{\"bold\":true},\"insert\":\"[Expected Clean Completion Date]\"},{\"insert\":\":\\n\"},{\"attributes\":{\"italic\":true},\"insert\":\"text\\\/number\"},{\"insert\":\"\\n=[Completion Date]@row + \\\" \\\" + [Completion Time]@row\\n\"}]","format":"rich","dateInserted":"2020-04-28T17:10:43+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/248173#Comment_248173","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/248173#Comment_248173

    Paul, I am also new to Smartsheet. I have the same problem statement, I want to get a date and time based on the given start date time and total hours spent.I tried the formula suggested by you but I could not accomplish it.

    · Share on Facebook Share on Twitter
  • Brenna Saunders
    Brenna Saunders ✭✭

    Hello - i am trying to create this same type of formula, but am getting some funny errors and not producing date values. Any ideas on how to resolve?

    SS- date formulas.PNG

    I thought it would also be helpful if i included my formulas:

    DR Actual Start:Date

    DR Actual Start Time: Text

    Actual Duration:Text

    Duration:=(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2

    Completion Date:=[DR Actual Start Time]@row + INT(INT([Duration 1]@row) / 24)

    Completion Time:=MOD(INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2), 24) + IF(MOD(INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2), 24) <> 12, IF(CONTAINS("p", IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")), -12), IF(CONTAINS("a", IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")), 12)) + ":" + IF(((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2 - INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2)) * 60 < 10, "0") + ((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [Duration 1]4), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2 - INT((VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) + IF(VALUE(LEFT([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) - 1)) <> 12, IF(CONTAINS("p", [DR Actual Start Time]2), 12), IF(CONTAINS("a", [DR Actual Start Time]2), -12)) + VALUE(MID([DR Actual Start Time]2, FIND(":", [DR Actual Start Time]2) + 1, 2)) / 60) + [Actual Duration]2)) * 60 + IF(MOD(INT([Duration 1]2), 24) >= 12, "pm", "am")

    Expected Completion Time:=[Completion Date]@row + " " + [Completion Time]@row

    · Share on Facebook Share on Twitter
  • Ben Goldblatt
    Ben Goldblatt Employee

    Hi@Brenna Saunders,

    It sounds like you're looking to be able to enter a start date, time, and duration to then have the actual end date and time calculated automatically. You may be able to leverage theDependency functionalityfor the date automation, but you would need to use formulas for the time calculations.@Paul Newcome可能是最好的资源,这在throu吗gh this thread (and feel free to chime in here Paul if you have a moment), but I highly recommendSubmitting an Enhancement Requestto see about having built-in "time tracking" features implemented in a future release.

    You may also want to look into ourPro Desk servicesfor coaching on these types of formulas. If you're not familiar with this, Pro Desk delivers practical one-on-one coaching sessions with a Smartsheet expert (30 minutes per session, 10 sessions per year) to accelerate your setup time and help you optimize your projects, programs and processes. A Pro Desk expert will provide coaching, guidance, best practices, and resources for further development.

    I hope this helps!

    Thanks,

    Ben

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ben GThanks for the tag!


    @Brenna SaundersTake a look through this threadHERE. There may be something you can use. I do remember helping someone set something very similar up at some point, but I can't remember if it is in the time thread or not.


    If not... Feel free to let me know. I am crazy busy with work at the moment, so it may take a little while to put something together, but it can be done.


    If I understand correctly... You basically want to manually enter your start date, start time, and duration and it will automatically calculate your end date and end time?

    10xViz.com

    · Share on Facebook Share on Twitter
  • mukul
    mukul ✭

    Hi@Paul Newcomeand Experts,

    I am new to the smart sheet and the community, so I need assistance calculating the End time based on the following conditions: Start time + Duration, as shown on this sheet, Duration can be expressed in days, hours or minutes (e.g. 1 day, 1 hour or .1 hour, or 60 minutes), factor 8 working days.

    image.png

    I tried using the logic / formula from this published sheet, giving me the error shown above when adding duration as 1 hour or .1h, or 60 minutes. In addition, it didn't convert to AM or PM based on the duration(perhaps I'm doing some wrong in the formula- not an expert)

    https://app.smartsheet.com/b/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc&_ga=2.24685626.1661211734.1652079367-686098908.1647358440

    Please paste your formula or publish

    I really appreciate your quick response and advice, which will help me out

    Regards

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @mukulYou would need to convert your duration into a standard measurement whether that be days, hours, or minutes.

    Hours:

    =IF(LEFT(Duration@row) = "d", VALUE(SUBSTITUTE(Duration@row, "d", "")) * 24, IF(LEFT(Duration@row) = "m", VALUE(SUBSTITUTE(Duration@row, "m", "")) / 60, VALUE(SUBSTITUTE(Duration@row, "h", ""))))


    From there you can use the formulas that will add a number of hours to a time.

    10xViz.com

    · 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 theFormula Handbook template!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/comment/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[]}">

Trending in Formulas and Functions

  • HAS exact match within multiselect - Numbered Values

    场景:试图确定一个match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…

  • I am having trouble using "And", "OR" & "Countif(s)" to build a formula.

    Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…

  • Help with date calculation formula

    Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…