Skip to Content
author's profile photo Former Member
Former Member

Converting Date/Time to Seconds

I have a report where I need to calculate the Avg Logged-In Time for a rep.

I got to the part where I take their log-in and log-out information to calculate the Logged-In duration. Now I need to add the duration and then divide it by the number of log-Ins.

Converting to Seconds- @ AgentstateTime

tonumber(left(totext(left(totext(time({@GMTTOCST}),"HH:mm:ss"),8)),2))3600+ tonumber(mid(totext(left(totext(time({@GMTTOCST}),"HH:mm:ss"),8)),4,2))60+ tonumber(right(totext(left(totext(time({@GMTTOCST}),"HH:mm:ss"),8)),2))

Duration

Next(AgentStateTime)-(AgentStateTIme)

LOG Duration AgentStateTime

Logged out 48 48577

Logged in 31 48,625

Logged out 27 48,656

Logged in 324 48,683

Logged out 22 49,007

I would like to add the duration field where LOG=Logged in then divided by the number of sessions.

(31+324) /2

My problem is it does not let me do a sum or a running total on the "Duration" field. Is there another way of achieving the same result?

Appreciate any help!

Thanks!

Achett13

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Nov 08, 2011 at 10:27 PM

    Hi Achett,

    I'm sure 'Duration' is a 'whileprintingrecords' formula and hence it wouldn't let you create a running total or a summary off it.

    You can, however, create an array and store the 'Duration'(s) for just the Logged in times.

    For eg:

    whileprintingrecords;
    numbervar array arr;
    numbervar i;
    if ({LOG} = "Logged in") then
    (
       i := i + 1;
       redim preserve arr<i>; 
       arr<i> := {Duration_field_or_formula};
    );
    ""

    Place this formula on the same section that displays the Duration, maybe towards the end

    Create another formula to find the average:

    whileprintingrecords;
    numbervar array arr;
    numbervar j;
    numbervar count;
    for j := 1 to ubound(arr) do
      c := c + arr[j];
    c/ubound(arr);

    Place this formula on the report footer/page footer.

    If the report is grouped on the Agent_Id or Name, then you would need to reset all the variables and the array used in the above formulas to zero at the group header.

    Let me know how this goes!

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 08, 2011 at 09:02 PM

    What is the format for your date_Time ?

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.