Quick 500 - Adding up Time in Crystal Reports

Posted on 2004-11-02
Medium Priority
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
  • 6
  • 4
  • 2
LVL 77

Expert Comment

ID: 12471564
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

ID: 12471866
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

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 77

Expert Comment

ID: 12472235
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

bdreed35 earned 2000 total points
ID: 12472258
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

ID: 12473145
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;
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);

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

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

Expert Comment

ID: 12473246
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

ID: 12473259
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

ID: 12473350
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

ID: 12473394
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

ID: 12474354
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.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…

621 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