Calculate Shift Time in Crystal

[Georgedooley]

Hi

I'm trying to set up a formula to calculate a shift time that crosses over midnight into 2 days. This formula will be used to count the number of scans recorded during this time.
Example. Shift Start Time 19:00, Shift End Time 03:00. Report will be used on a live dashboard through Crystal Server 2008. It will refresh every 5 mins between 19:00 and 03:00. This will give a live display of scan totals throughout the shift.
All help greatly appreciated.

 

[IanWaterman]

Are the Shift Start and end time fields DateTime?

If yes you can use

@2dayshift
date(endshift) > date (startshift)

This will return true or false and can then be used as required.

Ian

 

[Georgedooley]

hi Ian.
I can use the scan date and scan time as shift start and finish but i'm not sure how this will help me. I dont want to use a paramater or hardcode the shift date.
If i was running this report after the shift had finished it would be easy, but this report will be refreshing every 5 mins during the shift. Its when i pass midnight it gets tricky for me.

 

[IanWaterman]

Can you give more info as to what you are trying to do and why processing after midnight is a problem.

Ian

 

[Georgedooley]

Hi Ian
We have a process where barcodes are scanned. I want to setup a dashboard that will give a simple sum of all scan records since the shift start time. This report will be auto refreshed every 5 mins during the shift. This will keep the operators aware of their progress.
If the shift started at 19:00 and finised at 23:59:59 on the same day i could use something like;
if {@scan_date_time} in DateTime (today, 19, 00, 00) to DateTime (today, 23, 59, 59) then "Todays Shift"

But as it passes midnight i get a little lost. I hope this has explained what i'm trying to achieve.

 

[IanWaterman]

Please show formula

{@scan_date_time}

Ian

 

[Georgedooley]

datetime ({scaninfo1.scn-date}, timeserial (0,0,{scaninfo1.scn-time}))

 

[IanWaterman]

If you have a shift start and end date time why not use those.

if {@scan_date_time} in shiftstartdatetime to shiftenddatetime then "Todays Shift"

Ian

 

[Georgedooley]

hi Ian
what do i use as shiftstartdatetime? i dont want to hard code the date, i cant use "today". I dont want to have to keep changing the startdate/enddate daily. Maybe i'm missing your point, if so please let me know.
Thanks

 

[IanWaterman]

In order for this to work you must have a data set coveing the shift start and end times. I was assuming this was stored in a tbale which could be related to your table {scaninfo1.scn-date}.

Ian

 

[Georgedooley]

Thanks for the help Ian.
Any other ideas? I could maybe look at all scans for the past x hours as these scans only take place between 19:00 and 03:00. If i always look at the scans for the previous 8 hours i would pick everything up. Any suggestions how i can look at the last 8 hours, again passing midnight might be the problem.

 

[IanWaterman]

I misunderstood you I thought shift times were flexible. If they are always 1900 - 0300

you could do something like

If hour({@scan_date_time}) >= 19 or hour({@scan_date_time}) <= 3 then 'Todays Shift'

Ian

 

[Georgedooley]

hi Ian.
I wrote the following formula. I think it will work.

if currenttime in time (19,00,00) to time (23,59,59) then {@scan_date_time} >= datetime (today, time(19,00,00)) else if
currenttime in time (00,00,01) to time (03,00,00) then {@scan_date_time} >= datetime (today -1, time (19,00,00))