Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.

Calculating Durations

Eid E. Eid
Eid E. Eid ✭✭
edited 12/09/19 inArchived 2015 Posts

I have columns in my sheet with "Departure TIme" and "Arrival Time" in the format hh:mm

由于no "Time" Type for the columns, so I had to kep the Column Type as Number/Text

I am looking for a way to substract these 2 columns to obtain the duration of the trip.

Many thanks for any help

Tags:
· Share on Facebook Share on Twitter

Comments

  • SoniaR
    SoniaR ✭

    Hi Eid E. Eid -

    Currently, there isn't a way to use formulas with values formatted as hh:mm (as the : will make the value a text string). Our Product team is looking in to building a time formatted column type and I've just added your vote to this category on the request list.

    Thanks,

    Sonia

    · Share on Facebook Share on Twitter
  • Victor van R
    Victor van R ✭✭

    @SoniaRplease add my vote as well. This new functionality is much appreciated

    · Share on Facebook Share on Twitter
  • Joel Johnson
    Joel Johnson ✭
    edited 10/16/15

    Put my vote on this too.

    I have done a work around for simple duration calculations, and posted it in the below discussion:

    https://community.smartsheet.com/discussion/time-calculation

    · Share on Facebook Share on Twitter
  • Patrick Lawler
    Patrick Lawler ✭

    Eid and group,

    This exact problem can be solved usingAzuqua, an integration partner of Smartsheet. I work for Azuqua and the logging timestamp in a column based on a checkbox being marked (checked) is one of the many scenarios that we enable inside of Smartsheet.

    Using Azuqua you can set up a monitor for a change to column and in your particular case check to make sure the checkbox has been marked and then confirming the checkbox has been marked a timestamp / date will be entered into the column you specify.

    For your use case you would set up a checkbox for both departure and arrival. When you mark those checkboxes Azuqua will log a timestamp for each of those actions into the columns you specify and then using a formula you get the exact duration.

    If you want to learn more please visit our app listing at Smartsheet here:

    //m.santa-greenland.com/apps/azuqua

    Please feel free to follow up with any questions.


    -Patrick

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

@Jillian M<\/a> an IF statement will do what you need.<\/p>

=if([Column Name]@row=\"Value\",1,0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":107904,"type":"question","name":"How do I calculate different due dates on actions depending on the type of action?","excerpt":"I have created a sheet in which different type of issues are identified and assigned to owners. I have established three \"observation types\" that I would like to assign different due date. For example, a safety issues would need a due date of 2 working days from the date identified, and an Opportunity to Improve action…","snippet":"I have created a sheet in which different type of issues are identified and assigned to owners. I have established three \"observation types\" that I would like to assign different…","categoryID":322,"dateInserted":"2023-07-21T13:36:24+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T17:24:41+00:00","insertUserID":163745,"insertUser":{"userID":163745,"name":"dminor764","title":"CI Manager","url":"https:\/\/community.smartsheet.com\/profile\/dminor764","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-21T18:24:13+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-07-21T20:51:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3379908665,"url":"https:\/\/community.smartsheet.com\/discussion\/107904\/how-do-i-calculate-different-due-dates-on-actions-depending-on-the-type-of-action","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107904\/how-do-i-calculate-different-due-dates-on-actions-depending-on-the-type-of-action","format":"Rich","lastPost":{"discussionID":107904,"commentID":386475,"name":"Re: How do I calculate different due dates on actions depending on the type of action?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386475#Comment_386475","dateInserted":"2023-07-21T17:24:41+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-07-21T20:51:40+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-07-21T17:19:13+00:00","dateAnswered":"2023-07-21T13:48:32+00:00","acceptedAnswers":[{"commentID":386404,"body":"

This is going to be the general idea. You will want to replace the name of the column that you use for the date of the observation. I also didn't know the name of the third observation type or the time period.<\/p>

=IF([Observation Type]@row = \"Safety\", WORKDAY([Observation Date]@row, 2), IF([Observation Type]@row = \"Opportunity to Improve\", WORKDAY([Observation Date]@row, 14), IF([Observation Type]@row = \"Other Type\", WORKDAY([Observation Date]@row, 365), \"//m.santa-greenland.com/community/discussion/comment/\")))<\/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":107920,"type":"question","name":"How to remove text to only leave numbers","excerpt":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)) which works just fine, but for some reason the Conditional Formatting does not recognize the calculated output as a number to highlight…","snippet":"I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row)…","categoryID":322,"dateInserted":"2023-07-21T15:21:47+00:00","dateUpdated":null,"dateLastComment":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":96691,"lastUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3379907571,"url":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107920\/how-to-remove-text-to-only-leave-numbers","format":"Rich","lastPost":{"discussionID":107920,"commentID":386451,"name":"Re: How to remove text to only leave numbers","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386451#Comment_386451","dateInserted":"2023-07-21T15:31:04+00:00","insertUserID":96691,"insertUser":{"userID":96691,"name":"gwson","url":"https:\/\/community.smartsheet.com\/profile\/gwson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bb_am_gUV_w!XFjMnwg6CTQ!MSWtq5sIJTM","dateLastActive":"2023-07-21T15:30:31+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-07-21T15:35:25+00:00","dateAnswered":"2023-07-21T15:30:02+00:00","acceptedAnswers":[{"commentID":386449,"body":"

Try wrapping it in a VALUE()<\/p>

=VALUE(LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)))<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • True/False formula?

    I'm trying to figure out a formula to note a "1" in the cell if the information in the cell next to it is a certain value, any other value needs to show a "0". Is this a "true/false" formula? I figured this out: =IF([Question 1]@row = "2. I should avoid using the electronic device during the initial connection; however,…

  • How do I calculate different due dates on actions depending on the type of action?

    I have created a sheet in which different type of issues are identified and assigned to owners. I have established three "observation types" that I would like to assign different due date. For example, a safety issues would need a due date of 2 working days from the date identified, and an Opportunity to Improve action…

  • How to remove text to only leave numbers

    I have a column with numbers and letters i.e 1.1a, 1.1b, 2.1a and so on and using a simple formula to remove the letter and leave number =LEFT([SubComp#]@row, (LEN([SubComp#]@row) - 1)) which works just fine, but for some reason the Conditional Formatting does not recognize the calculated output as a number to highlight…