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

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!

Cells in an Excel spreadsheet



Best Answer

  • Cody Staub
    Cody Staub ✭✭✭
    Answer ✓

    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!

Answers