Calculating time between two timestamps in the same column

Jose Munoz

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 :-(

Juan Carlos Reguera

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.

Regards

Juan Carlos Reguera

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)

Regards.