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

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.