Calculating time between two timestamps in the same column

We were given some raw data from a virtual server and we need to calculate the session duration between 2 events in the same column and we cant figure it out the idea is to get the date from source table and on a formula sheet have the name of the agent, Date, Session Time start, Session Time End and Session duration

Table Source

Desired Table Outcome / Output

Any help will be greatly appreciated we lost the whole day :-(


  • Hi.

    In order to work with hours, minutes an seconds, I think you must change the value (text) in "Connected Time" and "Disconnected Time" to value (number), like:

    Hours= VALUE( MID([Connected_Time]1;1;2)

    Minutes= VALUE( MID([Connected_Time]1;4;2)

    Seconds= VALUE( MID([Connected_Time]1;7;2)

    You must use ever the format hh:mm:ss, and in order to facilitate yor calcules, use 24h format.

    Next, you can make calcules with this numbers.


  • So, working this way, you can obtain:

    Example of formule (calculation of minutes):

    =IF((VALUE(MID([Disconnected Time]1; 4; 2)) - VALUE(MID([Connected Time]1; 4; 2))) > 0; (VALUE(MID([Disconnected Time]1; 4; 2)) - VALUE(MID([Connected Time]1; 4; 2))); (VALUE(MID([Disconnected Time]1; 4; 2)) - VALUE(MID([Connected Time]1; 4; 2))) + 60)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!