Formulas for Calculating Time

Paul Newcome · 03/04/22

@GarrettDyerIt is a read only HTML sheet, so I cannot see any of the underlying formulas.

GarrettDyer · 03/04/22

@Paul Newcome Hereis the sheet. I worked on it quite a bit and think I have everything lined out. I'm not sure that I have any issues with it as of now. Except of the last two columns with Smartsheet changing the time to PST instead of the CST that is my current time zone.

Paul Newcome · 03/04/22

@GarrettDyerI believe there may also be a solution tucked away somewhere in this thread that should help you adjust for the time zone issue.

GarrettDyer · 03/04/22

@Paul NewcomeI have seen the formulas, the issue comes up that the change is not consistent to PST. If I write the formulas to make the adjustment and the times are in CST then I will have the issue for calculations, just reversed.

If you check the cell history in the "Time Changed" column you will see the events where Smartsheet Automation is making the changes and messing things up.

Jimrny · 05/17/22

@Paul Newcome

Any chance you can help? Trying to get the Duration to reflect the difference between start and stop (shown in minutes) in hh:mm if possible?

Jeff Reisman · 05/17/22

@JimrnyLooks like you've already got it. Just replace the " hrs " in your formula with ":" -

L_123 · 05/17/22

if you want to have the "HH" part of it, you need a conditional with a len to put a zero. Basically a duplicate of the formula

Jimrny · 05/17/22

@Jimrny<\/a> Looks like you've already got it. Just replace the " hrs " in your formula with ":" - <\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/PXCID720QBPG\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" Looks like you've already got it. Just replace the \\\" hrs \\\" in your formula with \\\":\\\" - \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/PXCID720QBPG\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/PXCID720QBPG\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\" \\n\"}]","format":"rich","dateInserted":"2022-05-17T15:13:17+00:00","insertUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2023-06-09T17:20:12+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\/329892#Comment_329892","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/329892#Comment_329892

Whoops, so the issue I'm getting is my duration seems off slightly.

Instead of 59 minutes, it's reflecting 1 hour and 59 minutes?

I am looking to reflecting "XX hrs XX mins" under duration. Seems to be rounding up and adding the hour before hand.

Update: It looks like it reflects the hour properly after 60 minutes. What could be causing the additional hour displayed at 59 minutes?

L_123 · 05/17/22

try using rounddown() instead of round

Jimrny · 05/18/22

Can anyone assist to see if this formula is correct for pulling minutes? For some reason I'm not sure if it's calculating the correct minutes.

Jeff Reisman · 05/18/22

@JimrnyFor some reason, when using MOD on a negative number, it's giving you the wrong portion of the remainder. MOD(-136, 60) should be 16, but it gives you 44. MOD(-148, 60) gives you 32 instead of 28. What these have in common is that if you subtract MOD's calculation from 60, you get the right number of minutes. 60-44 = 16, 60-32 = 28.

So add an IF to your formula to include that 60 minus calculation in when the[email protected]< 0 :

=IF([email protected]< 0, ROUND([email protected]/ 60, 0) + ":" +(60 -MOD([email protected], 60)), ROUND([email protected]/ 60, 0) + ":" + MOD([email protected], 60))

Jimrny · 05/18/22

@Jimrny<\/a> For some reason, when using MOD on a negative number, it's giving you the wrong portion of the remainder. MOD(-136, 60) should be 16, but it gives you 44. MOD(-148, 60) gives you 32 instead of 28. What these have in common is that if you subtract MOD's calculation from 60, you get the right number of minutes. 60-44 = 16, 60-32 = 28. <\/p>

So add an IF to your formula to include that 60 minus calculation in when the Difference@row < 0 :<\/p>

=IF(Difference@row < 0, ROUND(Difference@row \/ 60, 0) + ":" + (60 - <\/strong>MOD(Difference@row, 60)), ROUND(Difference@row \/ 60, 0) + ":" + MOD(Difference@row, 60))<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" For some reason, when using MOD on a negative number, it's giving you the wrong portion of the remainder. MOD(-136, 60) should be 16, but it gives you 44. MOD(-148, 60) gives you 32 instead of 28. What these have in common is that if you subtract MOD's calculation from 60, you get the right number of minutes. 60-44 = 16, 60-32 = 28. \\nSo add an IF to your formula to include that 60 minus calculation in when the Difference@row < 0 :\\n=IF(Difference@row < 0, ROUND(Difference@row \\\/ 60, 0) + \\\":\\\" + \"},{\"attributes\":{\"bold\":true},\"insert\":\"(60 - \"},{\"insert\":\"MOD(Difference@row, 60)), ROUND(Difference@row \\\/ 60, 0) + \\\":\\\" + MOD(Difference@row, 60))\\n\\n\"}]","format":"rich","dateInserted":"2022-05-18T16:59:16+00:00","insertUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2023-06-09T17:20:12+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\/330113#Comment_330113","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330113#Comment_330113

@Jeff Reisman, that worked out perfectly! Maybe you could assist with this? For some reason I believe anything over 30 - 59 minutes in Difference is showing an extra hour in Duration.

Paul Newcome · 05/19/22

@JimrnyDid you try@L_123's suggestion of using ROUNDDOWN instead of ROUND?

Jimrny · 05/20/22

@Jimrny<\/a> Did you try @L_123<\/a>'s suggestion of using ROUNDDOWN instead of ROUND?<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" Did you try \"},{\"insert\":{\"mention\":{\"name\":\"L_123\",\"userID\":16866}}},{\"insert\":\"'s suggestion of using ROUNDDOWN instead of ROUND?\\n\"}]","format":"rich","dateInserted":"2022-05-19T14:23:23+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-06-09T14:12:17+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\/330243#Comment_330243","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330243#Comment_330243

Just applied, will test and see at 30, 45 and 59 minutes.

BillH · 07/12/22

@Paul NewcomeI have read through all 10 of these pages (thanks so much for putting this all together) and I still am having issues. I have used the move row suggestion to get a date time stamp for when a request is completed. I then use a vlookup to get the completed date/time stamp into my main sheet. I am trying to calculate a TAT from Created to Completed, but a simple Completed-Created won't work. Most of the TAT will be same day, but many will not be. I have tried various other solutions, like the one to extract the date in one field & time in another for each of the Create and Completion date/time stamps. This works for the Created date but for the date I get from the vlookup I get an "Invalid Data Type". I have added +"" to the end of my Completed vlookup formula to get the time to populate. Any ideas on what I am doing wrong? Thanks so much for any help.

Formulas for Calculating Time

Comments

Categories