Timesheet report - need help with sums 2

[andie18]

It's me again. I still am working in this *#$%-ing report and I still need help with the last piece.

In case everyone has blocked this from their memory, let me give you the 411.

These people use GoldMine(dbase) to log in and out everyday. There are 4 things that they must record. They use the scheduled activity "next action" So when the come in they schedule a next action and put in the reference "Sign In". When they go to lunch "Sign Out For Lunch" when they come back "Sign In From Lunch" and when they leave "Sign Out". When they schedule these activities GoldMine time stamps it and sticks that value into {cal.createat}I need to display all of these times and then figure out how many hours they worked (which is Sign out - Sign In) I need it displayed properly such as 4 hours 25 min.

For the lunch sign in/out, I created a subreport to display those times. The main report is for the Sign In/Out. I have in the select expert:

{Cal.ONDATE}>={?Begin Date} and {Cal.ONDATE}<={?End Date}

I then have 3 groups:

1. {cal.createby} - that will produce the username
2. {cal.ondate} - to get the date
3. {cal.ref} - I have this one just showing Sign In/Out and discarding the others.

The report groups everything just fine. On my last post Ken Hamady suggested a formula to get the time worked:

If trim({cal.ref})=&quot;Sign Out&quot; then
TimeValue ({cal.Createat}) -
TimeValue (Previous({cal.Createat} ))

It is accurate but displays it in seconds instead of hours and minutes. I posted this on experts exchange and someone suggested the following formula:

numberVar TimeSpent;
numberVar Hours;
numberVar Minutes;

If trim({cal.ref})=&quot;Sign Out&quot; then
TimeSpent := TimeValue ({cal.Createat}) - TimeValue (Previous({cal.Createat} ))

Hours := Truncate(TimeSpent / 3600);
Minutes := Truncate(Remainder(TimeSpent, 3600) / 60);

ToText(Hours,00) + &quot;:&quot; + ToText(Minutes,00)

I can understand what it is trying to acheive but Crystal does not like the last 3 lines - the error is the remaining text does not appear to be part of the formula.

One last thing. The cal.createat is displayed in military time and is not a datefield it is a character field (or numeric text)

Any ideas? Sorry this is so long.

TIA
Andrea

 

[synapsevampire]

What is stored in:

&quot;When they schedule these activities GoldMine time stamps it and sticks that value into {cal.createat}&quot; Is it a datetime type, or?

And please post exactly what errors you're getting, not &quot;Crystal doesn't like it&quot;.

A timevalue is NOT a numeric, so you're probably experiencing Crystal's dislikes at the Timespent := line.

-k

 

[andie18]

I did list the error - 'the remaining text does not appear to be part of the formula.'

The error is at the hours :=

I did list hat cal.createat is - The cal.createat is displayed in military time and is not a datefield it is a character field - Did I state that wrong? I'm sorry if I did. It is a string based field(? - don't know if I said that right wither)

 

[betaanish]

hey...i feel u just need to add the code in curly braces...may not be curly, but i am sure it's a braces problem.
Try it out and let us know.

 

[Reebo99]

Replace :

numberVar TimeSpent;
numberVar Hours;
numberVar Minutes;

If trim({cal.ref})=&quot;Sign Out&quot; then
TimeSpent := TimeValue ({cal.Createat}) - TimeValue (Previous({cal.Createat} ))

Hours := Truncate(TimeSpent / 3600);
Minutes := Truncate(Remainder(TimeSpent, 3600) / 60);

ToText(Hours,00) + &quot;:&quot; + ToText(Minutes,00)

With:

numberVar TimeSpent;
numberVar Hours;
numberVar Minutes;

If trim({cal.ref})=&quot;Sign Out&quot; then
(TimeSpent := TimeValue ({cal.Createat}) - TimeValue (Previous({cal.Createat} ));
Hours := Truncate(TimeSpent / 3600);
Minutes := Truncate(Remainder(TimeSpent, 3600) / 60);
ToText(Hours,00) + &quot;:&quot; + ToText(Minutes,00));

Let me know how you get on......

Reebo
Scotland (Sunny with a Smile)

 

[Naith]

Andie,

Seems you want the time converted and then displayed sexily, so it's &quot;2 hours and 10 minutes&quot; rather than &quot;2:10&quot;. Right?

If that's pretty much it, copy and paste this and see what you get:

Whileprintingrecords;
Numbervar session_time_in_seconds := DateDiff('s',{@SignIn},{@SignOut}));

StringVar days_spent:= '';
Stringvar hours_spent:='';
Stringvar minutes_spent:='';
Stringvar seconds_spent:='';
Stringvar time_spent:='';

If session_time_in_seconds>86400 then
days_spent:=totext(truncate(session_time_in_seconds/86400),0)+' day' + if truncate(session_time_in_seconds/86400)>1 then 's ' else ' ';

If session_time_in_seconds>60 then
If session_time_in_seconds>86400 then
hours_spent:=totext((remainder(session_time_in_seconds,86400)/3600),0)+' hour'+ if ((remainder(session_time_in_seconds,86400)/3600))>1 then 's ' else ' '
Else
hours_spent:=totext(truncate(session_time_in_seconds/3600),0)+' hour'+ if truncate(session_time_in_seconds/3600)>1 then 's ' else ' '
Else
hours_spent:='';

If session_time_in_seconds>60 then
If session_time_in_seconds>3600 then
minutes_spent:=totext((remainder(session_time_in_seconds,3600))/60,0)+' minute'+ if ((remainder(session_time_in_seconds,3600))/60)>1 then 's ' else ' '
Else
minutes_spent:=totext(truncate(session_time_in_seconds/60),0)+' minute'+ if truncate(session_time_in_seconds/60)>1 then 's ' else ' '
Else
minutes_spent:='';

If session_time_in_seconds>60 then
seconds_spent:=totext(remainder(session_time_in_seconds,60),0)+' second'+ if remainder(session_time_in_seconds,60)>1 then 's' else ''
Else
seconds_spent:=totext(session_time_in_seconds,0)+' second'+ if session_time_in_seconds>1 then 's' else '';

Time_spent:=days_spent+hours_spent+minutes_spent+seconds_spent;

Hopefully, you will never need the bits in blue - unless you pay great overtime - so you can remove those parts if you like.

Naith

 

[Reebo99]

Naith,

Isn't this the same as using :
DateTimeDiff({@SignIn},{@SignOut})

Which would return something like :
189 Day(s), 15 Hour(s), 23 Minute(s), 16 Second(s)

But if you only wanted to show :
15 Hour(s), 23 Minute(s), 16 Second(s)

You would use :
mid(DateTimeDiff({@SignIn},{@SignOut}),instr(DateTimeDiff({@SignIn},{@SignOut}),&quot;Day(s)&quot+8)

This additional UFL can be found at :

http://support.crystaldecisions.com/communityCS/FilesAndUpdates/ufldtdif.exe.asp

Reebo
Scotland (Sunny with a Smile)

 

[alectek]

You may need &quot;;&quot; after IF statement.

Alex.

 

[Naith]

DateTimeDiff calculates differences between true date time fields.

The above formula calculates differences between fields represented as seconds - which is what Andie18 seemed to be working with. My initial conversion to seconds in the first couple of lines is so that I could test it.

Naith

 

[Reebo99]

Oh.

Reebo
Scotland (Sunny with a Smile)

 

[andie18]

Hi Everyone,

Thanks so much for the assistance. I can't wait to deliver this thing and move on ;o)

Reebo's formula worked fine. My only question is if the Sign in Time is 9:00 and the SIgn Out time is 10:01 it displays 1:1. If the Sign In time is 9:00 and the Sign Out time is 10:20 then it displays 1:20

I tried the formula that Naith wrote and I do not know what {@sign in}{@Sign out} is. Crystal did not recognize the field either (that is the error by the way)Can you please tell me what this is?

 

[Naith]

They're supposed to be whatever your fields are. I can't think where I must have picked them up from.

Replace this:

Numbervar session_time_in_seconds := DateDiff('s',{@SignIn},{@SignOut}));

with this:

Numbervar session_time_in_seconds := TimeValue ({cal.Createat}) - TimeValue (Previous({cal.Createat} ))

Basically, you should ensure that you assign whatever field is giving you your difference in seconds to session_time_in_seconds.

Naith

 

[andie18]

Naith -

you are too cool! It worked great. Thanks so much

 

[andie18]

one other question....

Is there a way to do a grand total? I have the formula for hours worked per day in group 2 footer which is cal.ondate. I was wonderng if I can put a grand total in group 1 footer which is cal.createby (which is the username)

 

[Naith]

If you had @FormulaA doing this:

If trim({cal.ref})=&quot;Sign Out&quot; then
TimeSpentTotal := TimeSpentTotal + TimeValue ({cal.Createat}) - TimeValue (Previous({cal.Createat} ));

you should be able to make a copy of the main formula I gave you a couple posts back, replacing this:

Numbervar session_time_in_seconds := TimeValue ({cal.Createat}) - TimeValue (Previous({cal.Createat} ))

with this:

Numbervar session_time_in_seconds := TimeSpentTotal;

Remember to make your variable names in the grand total formula copy different from your existing variable names in your group level formula.

Naith