Solved: Subtracting time using formulas

Options
Kyle Chipman
Kyle Chipman Overachievers
edited 04/28/21 in Formulas and Functions

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!

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!