How to input a date/time and calculate a time elapsed?

Cody Staub

I'm brand-spanking new to Smartsheet. We are trialing it for work and I'm trying to convert some of my Excel workbooks for use in this new platform. A really common formula for my work is

=(Time1-Time2)*1440

which gives me a total time elapsed in minutes. I then build my dashboards to take those calculated fields and do averages or whatnot. A bigger problem is that my times often cross different days (one common example is length of stay - which I calculate in hours because I have different reporting requirements if that is <24 hours).

The big question is: how do I get a cell to contain a date/time format?

When I import these formulas and data, none of the formulas work, and the date gets dropped from the data stored in the cells.

I'll attach a small example of cells from my Excel workbook so you can see what I mean. Any help is much appreciated. I won't really be able to use Smartsheet if I can't get this to work...which would be really disappointing.

Thank you!



Cody Staub

Still was getting the #UNPARSEABLE error. I tested each piece individually, and everything works separately except for this statement:

VALUE(LEFT[Start Time]@row, FIND(":", [Start Time]@row) - 1)

And I think I fixed it with adding a parenthesis after LEFT. This formula is working to get me elapsed time in minutes:

=(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + (([End Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60)))*60

Thank you!

Paul Newcome

Smartsheet does not currently have time based functions, but there are a number of workarounds.

Here is a thread that has a lot of time based solutions. Feel free to browse through them (I think there are currently 5 pages). If you can't find exactly what you need or need help adapting a solution to fit, feel free to let me know, and we can walk through some things together.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Cody Staub

@Paul Newcome Okay...I'm trying to consume all of this, but I'm sure I'll have questions. This seems like a lot of workaround for a pretty simple functionality...

Paul Newcome

@Cody Staub Smartsheet does not currently have time formatting/functions/calculations built in, but from my understanding they are working on it. In the meantime that means we are stuck with using workarounds. You have already cut out some of the more complicated bit by using 24 hours times.

Cody Staub

Hi @Paul Newcome

I have been attempting to use the formula you provided HERE, but I keep getting an #UNPARSEABLE error. I think I can get the calculations to work (I want the output in straight minutes), but I can't get that formula to parse to start testing it out.

I set up a test table exactly like the example and used this formula:

=(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT[Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2))/60)

Note: I also tried it removing that first parenthesis as it is in your formula, which didn't change anything. I added it because I noticed an extra closing parenthesis in the formula after that final 60.

Any help you're able to give is much appreciated. Thank you.

Paul Newcome

The closing parenthesis after the final 60 is supposed to be there and there are only supposed to be two opening at the front.

Make sure your column names in the formula match what you have in the sheet. I also do not see any date type columns in your screenshot, and the formula you are using incorporates date overlap.

Cody Staub

Thank you for the input. I think I changed the formula to match your feedback. On the date overlap piece...my data sometimes includes a date overlap, but not always. I need my final formula to work with either scenario.

Here's a screenshot of the sheet I'm testing. The two date columns are the Date data type. The others are the Text/Number data type

Here's what my formula currently looks like:

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT[Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2))/60)

Paul Newcome

Lets try shifting some of the parenthesis around a little bit and adding a few setts to really isolate each portion...

=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + (([End Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT[Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))

Cody Staub

Still was getting the #UNPARSEABLE error. I tested each piece individually, and everything works separately except for this statement:

VALUE(LEFT[Start Time]@row, FIND(":", [Start Time]@row) - 1)

And I think I fixed it with adding a parenthesis after LEFT. This formula is working to get me elapsed time in minutes:

=(((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + (([End Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60)))*60

Thank you!

Paul Newcome

My apologies. I didn't realize that was missing from the original formula when I was copy/pasting. Sorry about that.

Cody Staub

No worries...you were immensely helpful. I really appreciate all the time you took troubleshooting this with me. Thank you so much!