Link to home
Start Free TrialLog in
Avatar of jwebster77
jwebster77

asked on

Calculate time for records in a crystal report

I have a crystal report, develop in VB.  In the details section you would see rows of records like the following.

TIme In  2:00 AM  Time Out  3:00 AM  Elapsed  0.00
Time In 3:05 AM  Time Out 4:00 AM  Elapsed 5 minutes

etc.

I want to calculate the elapsed time between the current record and the one sitting above it.  Records will be sorted by time out
Avatar of Mike McCracken
Mike McCracken
Try this formula


WhilePrintingRecords;
Local NumberVar ElapsedSeconds;
If OnFirstRecord Then
    ElapsedSeconds := 0
Else
    ElapsedSeconds := DateDiff(''m', Previous({TimeOutField}), {TimeInField});

'Elapsed ' & CStr(ElapsedSeconds,'0') & ' minutes'

Open in new window


mlmcc
Avatar of jwebster77
jwebster77

ASKER

Hi there, I tried the below.

WhilePrintingRecords;
Local NumberVar ElapsedSeconds;
If OnFirstRecord Then
    ElapsedSeconds := 0
Else
    ElapsedSeconds := DateDiff('m', Previous({tblLabor.TimeOut}), {tblLabor.TimeIn});

'Elapsed ' & CStr(ElapsedSeconds,'0') & ' minutes'

The results are attached.  You will see where it did not evaluate and still shows zero.
idletimeexample.JPG
Avatar of jwebster77
jwebster77

ASKER

mlmcc any thoughts?  Thanks!
Avatar of Mike McCracken
Mike McCracken
Try this just to see what it thinks the previous time is
CHange your formula to

WhilePrintingRecords;
Local NumberVar ElapsedSeconds;
If OnFirstRecord Then
    "First Record"
Else
    CStr( Previous({tblLabor.TimeOut})

Open in new window

Avatar of jwebster77
jwebster77

ASKER

See attached.  I may also need to have the first record recognize for each employee now that I think of it.
Avatar of jwebster77
jwebster77

ASKER

file attached.
cr-example.JPG
Avatar of Mike McCracken
Mike McCracken
Here you go.  The problem with the previous formula is m is for months.  you have to use n for minutes.

WhilePrintingRecords;
Local NumberVar ElapsedMinutes;
If OnFirstRecord  OR (Previous({EmployeeID Field}) <> {EmployeeID Field}) Then
    ElapsedMinutes  :=  0
Else
    ElapsedMinutes :=  DateDiff('n', Previous({tblLabor.TimeOut}), {tblLabor.TimeIn});

'Elapsed ' & CStr(ElapsedMinutes,'0') & ' minutes'

Open in new window


If you need to total for each employee it will take 3 formulas
In the report header and group header add a formula
Name - DeclareVariables
WhilePrintingRecords;
Global NumberVar TotalMinutes;
TotalMinutes := 0;
''

Open in new window


Modify the one in the detail section

WhilePrintingRecords;
Local NumberVar ElapsedMinutes;
Global NumberVar TotalMinutes;

If OnFirstRecord  OR (Previous({EmployeeID Field}) <> {EmployeeID Field}) Then
    ElapsedMinutes  :=  0
Else
    ElapsedMinutes :=  DateDiff('n', Previous({tblLabor.TimeOut}), {tblLabor.TimeIn});

TotalMinutes := TotalMinutes + ElapsedMinutes;
'Elapsed ' & CStr(ElapsedMinutes,'0') & ' minutes'

Open in new window


IN the group footer add a formula
Name - PrintTotalMinutes
WhilePrintingRecords;
Global NumberVar TotalMinutes;
TotalMinutes

Open in new window


mlmcc
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.