Days Hours Minutes and Seconds

L_123

So apparently smartsheet created and modified columns retain time down to at least the seconds, I wasn't willing to keep checking beyond that. I utilized this to report the time difference in form submissions down to the seconds. Just sharing in case it helps someone. Of course you can break this down in any way you want to report the information.

="D:" + INT(Created2 - Created1) + " H:" + INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36) + " M:" + INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) + " S:" + INT(60 * (((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) - INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)))

Days:

INT(Created2 - Created1)

Hours

INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)

Minutes

INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)

Seconds

INT(60 * (((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) - INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)))

Mike Wilday

Wow. @L@123 Nicely done. Too much math for my tastes. Still hoping to see some good time formulas introduced soon.

Mike Wilday

Definitely not me.

Andrée Starå

@L@123

Wow!

https://media.giphy.com/media/1ym5LJ17vp77BL8X5O/giphy.gif

L_123

Thanks. The math wasn't the difficult part of this, it was the formatting. This started because i subtracted 2 created by cells and got a numerical value.

example:

1.05104

means that the second reference is 1 day and 5104 seconds after the first. After I understood that it wasn't an issue. This also means that you can shorten date calculations in modified/created columns using this method.

=int(created2 - created1)

will give you the days between created2 and created1 instead of having to use the netdays formula.

Paul Newcome

I remember noticing the same thing a while back getting a numerical value when subtracting the created from the modified but got sidetracked (and then forgot about it) before I could do further testing. Thanks for the reminder. Hahaha.

L_123

Yeah i've noticed this several times, this was just the first time I saw it at the end of the day and decided to figure it out hahaha. I'm fairly confident this goes to milliseconds and further, as I had to round at the seconds level. To me this means they definitely intend to add time functions to the program. I just hope they don't add a secondary program to add another value stream to smartsheet like they did with 10k' for actual resource allocation.

Paul Newcome

That has actually been like that for as long as I can remember. As for adding in time functions... Everything that they would need is already in place. They just need to expand it is all.

Mike Wilday

@L@123 I agree. Increasing value to the base product would be ideal rather than increasing profit margins.