Quick 500 - Adding up Time in Crystal Reports

Posted on 2004-11-02
Last Modified: 2012-06-22
Ok, Just an easy 500 needed....

I have a bunch of fields in quite a badly designed database, which detail the time worked, these include

Start_Mon, Finish_Mon, Lunch_Mon
Start_Tues, Finish_Tues, Lunch_Tues
Strat_Wed, Finish_Wed, Lunch_Wed

etc etc right up to Sunday.

For some sample data, i have:

Start_Mon = 08:50 (am)
Finish_Mon = 17:00 (pm)
Lunch_Mon = 60

Start_Tues = 08:45
Finish_Tues = 17:00
Lunch_Tues = 125 (2h 5m)

Start_Wed = 8:50
Finish_Wed = 17:30
Lunch_Wed = 60

I basically need all these hours added up, the lunch subtracted and then a total hours displayed.

For a little bit of help, i already have the sum to add up all the Decimal to time from a datediff statement, it's really just the actuall adding two times together that i'm having difficulty on :/

My sum for adding up the total hours for one actualy day is as follows:

NumberVar Decimal := 0;
NumberVar Hours := 0;
NumberVar Mins := 0;

if isnull({CReports_Bible_Summery.start_mon}) or isnull({CReports_Bible_Summery.lunch_mon}) or isnull({CReports_Bible_Summery.end_mon}) then
     Decimal := 0
     Decimal := ((datediff("n",{CReports_Bible_Summery.start_mon},{CReports_Bible_Summery.end_mon})) - {CReports_Bible_Summery.lunch_mon}) /60;
Hours := Truncate(Decimal);
Mins := Decimal - Hours;
Mins := Mins * 60;
Hours+(Mins / 100)

Now this displays the Total hours in another column elsewhere, what i'm aiming for is a total Column that adds up ALL of these columns and displays the correct hours and minutes (Remember you can't just add them up in decimal)

Thanks guys!
Question by:Cloud9_User
    LVL 77

    Expert Comment

    Hello Cloud9_User,

    Are the days entries coming into CR as separate records or do we have all the days for one week on one record?


    Author Comment

    Basically all the rows that you need should be in the same row of the Database, you literally have the Monday - Sundat start dates, finish dates and Lunch breaks listed in the Fields in the report.

    Sorry about the Slow response :)

    Author Comment

    Getting all the Information isn't the Problem, just assume you have it.... its the Mathematics that i'm struggling on :/

    LVL 77

    Expert Comment

    Well this is how I would do it:

    Numbervar TotTime;
    Numbervar TotStart;
    numbervar totLunch;

    TotLunch := ({Table8.MonLunch}+{Table8.TueLunch}+{Table8.WedLunch}+{Table8.ThuLunch}+{Table8.FriLunch})/60;

    totstart:= datediff("s",{Table8.MonStart},{Table8.MonFinish});
    totstart:= totstart + datediff("s",{Table8.tueStart},{Table8.tueFinish});
    totstart:= totstart + datediff("s",{Table8.wedStart},{Table8.wedFinish});  
    totstart:= totstart + datediff("s",{Table8.thuStart},{Table8.thuFinish});  
    totstart:= totstart + datediff("s",{Table8.friStart},{Table8.friFinish});  
    totstart := totstart/60/60;
    totTime:= totstart -totlunch;

    LVL 28

    Accepted Solution

    I altered your Monday formula a bit.  Just add the week_total_mins stuff to each days formula so that you accumulate the total as you go.  At the end create a new formula that uses that formula and do the same formatting that you are doing with the results in the other formulas.

    local NumberVar TotalMins := 0;
    local NumberVar Hours := 0;
    local NumberVar Mins := 0;
    global numbervar week_total_mins;

    if isnull({CReports_Bible_Summery.start_mon}) or isnull({CReports_Bible_Summery.lunch_mon}) or isnull({CReports_Bible_Summery.end_mon}) then
         TotalMins := 0
         TotalMins := (datediff("n",{CReports_Bible_Summery.start_mon},{CReports_Bible_Summery.end_mon})) - {CReports_Bible_Summery.lunch_mon};
    week_total_mins := week_total_mins + TotalMins;
    Hours := int(TotalMins/60);
    Mins := remainder(TotalMins,60);

    Hours+((Mins*60) / 100)

    Author Comment

    Hmmm..... bdreed, It looks ok, But i don't understand the Global Variable thing....

    Can't i do it in just one formula? For example, i have this at the moment, but its proving a little buggy:

    NumberVar Decimal:= 0;
    // MONDAY
    if not(isnull({CReports_Bible_Summery.start_mon}) or isnull({CReports_Bible_Summery.lunch_mon}) or isnull({CReports_Bible_Summery.end_mon})) then
        Decimal := Decimal +  ((datediff("n",{CReports_Bible_Summery.start_mon},{CReports_Bible_Summery.end_mon}) - {CReports_Bible_Summery.lunch_mon}) /60);

    // TUESDAY
    if not(isnull({CReports_Bible_Summery.start_tue}) or isnull({CReports_Bible_Summery.lunch_tue}) or isnull({CReports_Bible_Summery.end_tue})) then
        Decimal := Decimal +  ((datediff("n",{CReports_Bible_Summery.start_tue},{CReports_Bible_Summery.end_tue}) - {CReports_Bible_Summery.lunch_tue}) /60);

    if not(isnull({CReports_Bible_Summery.start_wed}) or isnull({CReports_Bible_Summery.lunch_wed}) or isnull({CReports_Bible_Summery.end_wed})) then
        Decimal := Decimal +  ((datediff("n",{CReports_Bible_Summery.start_wed},{CReports_Bible_Summery.end_wed}) - {CReports_Bible_Summery.lunch_wed}) /60);

    if not(isnull({CReports_Bible_Summery.start_thu}) or isnull({CReports_Bible_Summery.lunch_thu}) or isnull({CReports_Bible_Summery.end_thu})) then
        Decimal := Decimal +  ((datediff("n",{CReports_Bible_Summery.start_thu},{CReports_Bible_Summery.end_thu}) - {CReports_Bible_Summery.lunch_thu}) /60);

    if not(isnull({CReports_Bible_Summery.start_fri}) or isnull({CReports_Bible_Summery.lunch_fri}) or isnull({CReports_Bible_Summery.end_fri})) then
        Decimal := Decimal +  ((datediff("n",{CReports_Bible_Summery.start_fri},{CReports_Bible_Summery.end_fri}) - {CReports_Bible_Summery.lunch_fri}) /60);

    if not(isnull({CReports_Bible_Summery.start_sat}) or isnull({CReports_Bible_Summery.lunch_sat}) or isnull({CReports_Bible_Summery.end_sat})) then
        Decimal := Decimal +  ((datediff("n",{CReports_Bible_Summery.start_sat},{CReports_Bible_Summery.end_sat}) - {CReports_Bible_Summery.lunch_sat}) /60);

    if not(isnull({CReports_Bible_Summery.start_sun}) or isnull({CReports_Bible_Summery.lunch_sun}) or isnull({CReports_Bible_Summery.end_sun})) then
        Decimal := Decimal +  ((datediff("n",{CReports_Bible_Summery.start_sun},{CReports_Bible_Summery.end_sun}) - {CReports_Bible_Summery.lunch_sun}) /60);

    //Return the Decimal instead of the Time
    numbervar decimal_val := 0;
    select (decimal-int(decimal))
        case is < .25 : decimal_val := .0
        case is < .50 : decimal_val := .25
        case is < .75 : decimal_val := .50
        default          : decimal_val := .75;

    int(decimal) + decimal_val

    Recognise the Case statement by the way? ;)

    Author Comment

    Ignore the '//CONVERT THE DECIMAL TO TIME' bit... that section has been removed.
    LVL 28

    Expert Comment

    You can do it all in one formula, I was under the impression that you needed the hours for the individual days and if so, why recalculate each days minutes again?

    In your formula, instead of dvidiing by 60 for each day, just tally the minutes.
    You can divide by 60 once at the end.

    You say that it is buggy, can you describe what you mean by that?
    LVL 28

    Expert Comment

    BTW, the global variable allows you to share the variable between formulas.
    Local only allows that formula to be used in that formula.

    If you don't define the scope (local, global, or shared), then global is assumed.

    Author Comment

    Lol..... Well, it turns out that in the last 30 minutes we found the problem,

    There was one instance that the hours and minutes were coming out wrong and the main VB engine was to blame :/ hence i was trying to rewrite my total's formula, even though that one worked fine!

    Sorry to waste your time.... Even tho, Yet again bdreed, you were ready to come to my rescue :P

    I'll give you the points anyways... seeing as technically you were right!


    Author Comment

    And thanks for the definition of the Global variable.... i understand what its used for, but i'm just a little confused about when, or rather the order in which, the formula would be calculated, i.e. if i have formula one... that works out the final Global variable, how do i stop any other formula's using that global variable from being called first etc. And what happens if a public variable is being calculated by two different formulas?

    LVL 28

    Expert Comment

    You can force the order of the formula calculations by using EvaluateAfter() in the beginning of your formulas:

    //@Sunday - don't use EvaluateAfter since this should calc first.
    //@Monday - use "EvaluateAfter({@Sunday})" to force it to wait until @Sunday calcs
    //@Tuesday - use "EvaluateAfter({@Monday})" to force it to wait until @Sunday calcs

    If you change the value of a global variable in one formula, any other formula that uses that variable will see the changed value.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video discusses moving either the default database or any database to a new volume.

    931 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now