Time Calculation in Crystal Reports 1

[CarlaBee]

Hi,
I have to do a time calculation for a report based on operators punches in Vantage 6.1. We run a 24 hr day, with shifts that range from 8 hrs, 10 hrs, 12 hrs, etc. It varies.

Vantage calculates the time on a 24 hr day (7 p.m. = 1900)

If someone clocks in at 18.75, I need my report to show 18:45 (or 7.50, show 7:30), etc. I need the minutes only to calcuate based on a 60 minute hour,not hundreths. I know I have to split the hours and minutes, calculate and put back together.

The problem is I'm very new to Crystal and I'm cluesless on how to do this. Can anyone help?

Thanks in advance.

Carla

 

[rosemaryl]

Dear Carla,

To convert 100ths to minutes you multiply by 60.


So here you go:

NumberVar TTime := 18.75; //put your time field here
Time(Truncate(TTime), (Remainder(TTime,1) * 60), 0);

Once you place the field on your report, you can format it to display the way you want (24 Hour with no seconds would be 18.45.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CarlaBee]

Ro, thanks, but it doesn't seem to be working. The formula keeps coming back with an error.

Following I've pasted the formula exactly as I've written it replacing my field. Help. I've been working on this for hours...grrrr.

NumberVar ttime = {LaborHed.ActualClockInTime}

Time (truncate (ttime), (remainder (ttime,1) * 60), 0)

 

[rosemaryl]

Dear Carla,

Ok, you are missing the semicolons at the end of the statements.

Notice in my post:

NumberVar TTime := 18.75[red];[/red] //put your time field here

So, simply change yours to:

NumberVar ttime = {LaborHed.ActualClockInTime};
Time (truncate (ttime), (remainder (ttime,1) * 60), 0);

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CarlaBee]

Thanks again Ro, but now it's just telling me that everyone clocked in at 12:00. I think I'm going to give up for today. I appreciate your help.
thanks

 

[rosemaryl]

Dear Carla,

Don't give up yet....

I assumed that the field was the way you showed it 18.75. Now is that how the data actually looks.

I need to know what this field type is.

Now, please show me exactly the format of your {LaborHed.ActualClockInTime} field. Place it on the report, preview and then copy that and paste it into the message. If it is not formatted that way two digits decimal and two digits then I need examples from which to build an accurate formula.

Regards,

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CarlaBee]

Ok, one more try. Here's a sample.

Act in Adj in Act Out Adj Out Total Hrs

18.70 19:00 6.75 6:45 11.25

6.75 07:00 15.50 15:30 8.00

Here goes. If you look at the second line, the actual time clocked in shows 6.75 but in "our time" is 6:45. The Adjusted in is automatic (more or less) based on a shift code set in the database. The adjusted times print out correctly, but the actual times print out in hundreths.

What this report should look like is:

18:42 19:00 6:45 6:45 11.25

6:45 07:00 15:30 15:30 8.00

Hope this makes sense. Prior to Crystal, we used progress report builder. I was able to make it work by a stringing and convering the hours and minutes separately and putting them back together. The functions in Crysal are very different and I'm a bit clueless.

Thanks again for your help!

 

[lyanch]

You need a : in your = to assign it.

NumberVar ttime := {LaborHed.ActualClockInTime};

otherwiuse it will just be 0 (12:00)

Lisa

 

[CarlaBee]

Thanks Lisa but now I have an error that says "numeric overflow" I need a vacation (and a raise). LOL

 

[CarlaBee]

Ro, I forgot to mention that the Actual time field is a decimal field, the adjusted is a character field.

 

[synapsevampire]

Try the following:

(join({table.field},":")[2])*.6

If you get an error, please state what the error is.

-k

 

[CarlaBee]

K, thanks. Says a string array is required.

 

[rosemaryl]

Dear Carla,

Please paste the formula in that you are using now.

It should read:

NumberVar ttime := {LaborHed.ActualClockInTime};
Time (truncate (ttime), (remainder (ttime,1) * 60), 0);

Now, if your formula looks like and you receive a numeric overflow .. that shouldn't happen.

thanks,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CarlaBee]

Same thing. Numeric overflow. I did cut and paste your formula earlier and didn't get that message but now I am. I think my system is tired and probably having some memory issues at this point. No sense proceeding until I give it a reboot. Crystal tends to crash on me now and then when I work on it for long periods. Plus it's time to get my butt outta here. I've had more fun that I wanted to. LOL. I'll give it another shot tomorrow and see what happens. Wish me luck. I appreciate all your help.

 

[rosemaryl]

Dear Carla,

I figured out the problem. I started trying large numbers thinking at first maybe you had bad data, then I happened to try 23.96 and boom numeric overflow.

The problem is with times after 23:95 (11:57!).. that produces the numeric overflow. Let me work on it a little and I will have something for you tomorrow.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

Dear Carla,

Here you go and my apologies for not testing thoroughly enough. I am going to plead meds as I am recovering from Scarlet Fever!

Now this will give you House:Minutes if you also needs seconds, please advise.

NumberVar ttime := {LaborHed.ActualClockInTime};
Numbervar H := Truncate(ttime);
Numbervar M := Truncate(remainder (ttime,1) * 60);

Time(h, m, 0);

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Oooops:

val(split("12:75",":")[2])*.6

Replace the "12:75" with your table.field

-k

 

[CarlaBee]

Ro,
IT WORKS! Thank you, thank you.
Thanks to you and to synapsevampire (and anyone else that contributed) for your help. It's been much appreciated.

Geez, now I can actually get some of my other work done today.

Thanks again,
Carla