[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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