Days Hours Minutes and Seconds

Options
L_123
L_123 ✭✭✭✭✭✭
edited 05/28/20 in Formulas and Functions

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)))

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!