Solved: Subtracting time using formulas

Kyle Chipman

Published Time of Day Conversion and Calculation sheet

https://app.smartsheet.com/b/publish?EQBCT=6d765f7f8a864676b3b5d5f8fd866bf1

Original "Subtracting time using formulas" thread

https://community.smartsheet.com/discussion/3795/subtracting-time-using-formulas

@Shawn Roberts @J. Craig Williams

I searched for "Subtracting time using formulas" and came across your thread a few years back. As I needed this functionality for a solution I'm building I went ahead and solved for it natively within Smartsheet (a working methodology, at the very least). It requires a Day01 or Day02 calculation on the sheet itself, as well as a 24 hour and a series of INDEX MATCH calls, but the fundamentals work. I put together a published version that carries the formulas and columns you would need to locate on your own sheet, as well as the INDEX MATCH columns you'll need to host yourself.

I only put together a version for 15 minute time intervals; this could work minute-by-minute, but whatever, 15 is all I need and I didn't want to type more than necessary.

Final thought; I could have gone the Bridge route as well, but I haven't really started working with that tool yet and I was genuinely curious if I could figure it out.

Let me know if you have any questions or proposed improvements (I feel like one set of columns could be shorn, but again, just wanted to get the thing done). Hope it helps!

Andrée Starå

Hi @Kyle Chipman

I hope you're well and safe!

Nicely done! 👍️ Sounds interesting!

I've requested access.

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Kyle Chipman

Shoot, my apologies - I pasted the URL as opposed to the published editable link (force of habit). Can't edit my original post.

https://app.smartsheet.com/b/publish?EQBCT=6d765f7f8a864676b3b5d5f8fd866bf1

Let me know if you have any thoughts! It's also got my stock-standard Ancestor, Children, Ancestor Collect and Ancestor Primary, Secondary and Tertiary Category columns. These are the building blocks for all my sheets and mad helpful universal utility columns. You can slice and dice a ton when it comes to formulas, automations and conditional formatting with these in place.

All the back-end columns are left unhidden, tucked underneath the Freeze Column.

And thank you - hope you're well and safe too :). Getting my second dose vaccine today. Couldn't be more grateful or excited!!!