Results 1 to 8 of 8

Thread: How to calculate duration of time using crystal reports? PLS HELP

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    20

    How to calculate duration of time using crystal reports? PLS HELP

    I am using Crystal Reports Basic for Visual Studio 2008 and backed by SQL Server as my database.

    My application is a Employee Punch Card System that will insert each transaction into database. In database, i have these tables: Dateandtime, EmployeeName & Transtype

    Note: Each transaction type is assigned a different number.

    For example:
    Employee Clocks in = 1
    Employee Clocks Out = 2

    When i generate report, it displays all transaction in order of the dateandtime field.

    Now my problem is: How to calculate and display the total number of hours worked by the employee?

    ( transtype 2 subtract transtype 1 = total number of hours worked )

  2. #2
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: How to calculate duration of time using crystal reports? PLS HELP

    This is not the solution to your problem, but it will help you

    How do you create a formula in Crystal Reports (CR) that adds more than
    one time field and displays the total in the same format: hh:mm:ss?

    For example:
    1:45:01 + 1:45:01 should display 03:30:02 and not 2:90:02

    Solution
    In order to get the sum of more than one time field and to display the
    total of the time field in the same format (hh:mm:ss) you must complete
    the following steps:

    A. Convert all the time fields to a common time unit such as seconds
    B. Calculate the total time in seconds
    C. Convert the seconds back to hh:mm:ss format.


    A.-Convert time fields to seconds
    --------------------------------
    Convert the time fields to seconds so that a common time unit is used to
    sum up all three different time units.

    If your field is a datetime field, complete the following steps:
    1. Create a new formula and call it @ConvertTimeToSeconds
    2. To convert the datetime field to seconds, create a formula similar to
    the following:
    Code:
    local numbervar hours;  
    local numbervar minutes;  
    local numbervar seconds;  
     
    // Convert the hours to seconds by multiplying by 3600  
    hours := hour({@time}) * 3600;  
     
    // Convert the minutes to seconds by multiplying by 60  
    minutes := minute({@time}) * 60;  
    seconds := second({@time});  
     
    //add up all the seconds  
    hours + minutes + seconds;
    If your field is string field with the format of hh:mm:ss to convert to
    seconds complete the following steps:
    1. Create a new formula and call it @ConvertTimeStringToSeconds
    2. Create a formula similar to the following to convert the string field
    to seconds:
    Code:
    local numbervar hours;  
    local numbervar minutes;  
    local numbervar seconds;  
     
    // Parse out the hours portion of  the string and multiply by 3600 to convert to seconds  
    hours := tonumber({timestringfield}[1 to 2])* 3600;  
     
    // Parse out the minutes portion of  the string and multiply by 60 to convert to seconds  
    minutes := tonumber({timestringfield}[4 to 5]) * 60;  
     
    // Parse out the seconds  
    seconds := tonumber({timestringfield}[7 to 8]);  
     
    // Add up all the seconds  
    hours + minutes + seconds;

    B.-Calculate the total time in seconds
    ------------------------------------
    Create a summary formula that will sum the
    @ConvertTimeToSeconds or @ConvertTimeStringToSeconds
    1. Create a new formula and call it @TotalSeconds
    2. To sum up either formula, create a formula similar to the following:
    [Code]
    sum(@ConvertTimeToSeconds, Group)
    - OR -
    sum(@ConvertTimeStringToSeconds, Group)


    C.-Convert the seconds back to hh:mm:ss format.
    --------------------------------------------
    Create a formula that converts the @TotalSeconds results back to
    hh:mm:ss format:
    1. Create a new formula and call it @ConvertTotalSeconds
    2. To convert the results from @TotalSeconds back to hh:mm:ss format,
    create a formula similar to the following:
    Code:
    local numbervar RemainingSeconds;  
    local numbervar Hours ;  
    local numbervar Minutes;  
    local numbervar Seconds;  
     
    //divide the @TotalSeconds by 3600 to calculate  hours. 
    //Use truncate to remove the decimal portion.  
    Hours := truncate({@TotalSeconds} / 3600);  
     
    // Subtract the hours portion to get RemainingSeconds  
    RemainingSeconds := {@TotalSeconds} - (Hours * / 3600);  
     
    // Divide RemainingSeconds by 60 to get minutes. 
    //Use truncate to remove the decimal portion.  
    Minutes := truncate(RemainingSeconds/60);  
     
    // Subtract the Hours and Minutes and what is left over is seconds.  
    Seconds := {@Totalseconds} - (Hours * 3600) - (Minutes * 60);  
     
    // Format the hours, minutes, and seconds to hh:mm:ss  
    totext(Hours,"00") + ":" + totext(Minutes,"00") + ":" + totext(Seconds,"00")
    JG

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    20

    Re: How to calculate duration of time using crystal reports? PLS HELP

    thanks for the effort trying to solve this question. but im struggling to get it right...

  4. #4
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: How to calculate duration of time using crystal reports? PLS HELP

    Also, You can try the next function (check out CR help for details)
    Code:
    DateDiff("h", {datetime1}, {datetime2})

  5. #5
    New Member
    Join Date
    Dec 2005
    Posts
    1

    Re: How to calculate duration of time using crystal reports? PLS HELP

    Hi,

    I'm doing something similar. Except in my table it will create a line for each "Swipe/Tag" as In or Out.

    Now what if there were multiple in's and out's. How do I calculate the hours for the day and then per week?

    Thanks

    Pieter

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Oct 2009
    Posts
    20

    Re: How to calculate duration of time using crystal reports? PLS HELP

    Quote Originally Posted by pjjdp View Post
    Hi,

    I'm doing something similar. Except in my table it will create a line for each "Swipe/Tag" as In or Out.

    Now what if there were multiple in's and out's. How do I calculate the hours for the day and then per week?

    Thanks

    Pieter
    thats why im posting the question here.. i also struggle

  7. #7
    New Member
    Join Date
    Sep 2013
    Posts
    1

    Re: How to calculate duration of time using crystal reports? PLS HELP

    JGGT - Kudos! Your solutions was beautiful, and works like a champ. I had some varchar fields in hh:mm:ss format that needed to subtracted, and your codes worked beautifully with very little tweaking. I realize that I am responding well after-the-fact, but wanted to give props where due.

    Kristina

  8. #8
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: How to calculate duration of time using crystal reports? PLS HELP

    Kristina, I'm glad that it helped You !
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width