Quick 500 - Adding up Time in Crystal Reports

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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?

Cloud9_UserAuthor Commented:
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 :)
Cloud9_UserAuthor Commented:
Getting all the Information isn't the Problem, just assume you have it.... its the Mathematics that i'm struggling on :/

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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;

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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud9_UserAuthor Commented:
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? ;)
Cloud9_UserAuthor Commented:
Ignore the '//CONVERT THE DECIMAL TO TIME' bit... that section has been removed.
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?
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.
Cloud9_UserAuthor Commented:
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!

Cloud9_UserAuthor Commented:
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?

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

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.