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

Pete

Solved

Posted on 2004-11-02

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:

//Monday

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

else

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!

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:

//Monday

NumberVar Decimal := 0;

NumberVar Hours := 0;

NumberVar Mins := 0;

if isnull({CReports_Bible_Sum

Decimal := 0

else

Decimal := ((datediff("n",{CReports_B

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!

12 Comments

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

Pete

Sorry about the Slow response :)

Numbervar TotTime;

Numbervar TotStart;

numbervar totLunch;

TotLunch := ({Table8.MonLunch}+{Table8

totstart:= datediff("s",{Table8.MonSt

totstart:= totstart + datediff("s",{Table8.tueSt

totstart:= totstart + datediff("s",{Table8.wedSt

totstart:= totstart + datediff("s",{Table8.thuSt

totstart:= totstart + datediff("s",{Table8.friSt

totstart := totstart/60/60;

totTime:= totstart -totlunch;

totTime

Pete

//Monday

local NumberVar TotalMins := 0;

local NumberVar Hours := 0;

local NumberVar Mins := 0;

global numbervar week_total_mins;

if isnull({CReports_Bible_Sum

TotalMins := 0

else

TotalMins := (datediff("n",{CReports_Bi

week_total_mins := week_total_mins + TotalMins;

Hours := int(TotalMins/60);

Mins := remainder(TotalMins,60);

Hours+((Mins*60) / 100)

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

Decimal := Decimal + ((datediff("n",{CReports_B

// TUESDAY

if not(isnull({CReports_Bible

Decimal := Decimal + ((datediff("n",{CReports_B

// WEDNESDAY

if not(isnull({CReports_Bible

Decimal := Decimal + ((datediff("n",{CReports_B

//THURSDAY

if not(isnull({CReports_Bible

Decimal := Decimal + ((datediff("n",{CReports_B

//FRIDAY

if not(isnull({CReports_Bible

Decimal := Decimal + ((datediff("n",{CReports_B

//SATURDAY

if not(isnull({CReports_Bible

Decimal := Decimal + ((datediff("n",{CReports_B

//SUNDAY

if not(isnull({CReports_Bible

Decimal := Decimal + ((datediff("n",{CReports_B

//CONVERT THE DECIMAL TO TIME

//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? ;)

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?

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.

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!

//@Sunday - don't use EvaluateAfter since this should calc first.

//@Monday - use "EvaluateAfter({@Sunday})"

//@Tuesday - use "EvaluateAfter({@Monday})"

etc..

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

By clicking you are agreeing to Experts Exchange's Terms of Use.

This video discusses moving either the default database or any database to a new volume.

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

Connect with top rated Experts

**14** Experts available now in Live!