Breaking down time fields in Crystal

[sorchard2000]

I have a Crystal report that deals with time fields. I need the report to show the total number of minutes that were in the 7a-3p shift, 3p-7p shift, and 7p-11p shift. My problem is that some cases are between shifts (for example: 14:15-16:00). I need to split those particular cases so that (in the above example) 00:45 minutes would be credited to the 7a-3p shift and 01:00 (60 minutes) would be credited to 3p-7p shift. This has been very challenging for us. Any ideas?

example:
Total # cases | Total minutes 7a-3p | Total minutes 3p-7p|
5 | 321 | 139 |


Thanks!
sorchard2000

 

[lbass]

How do these fields appear in the database? For example, is there one field (a string) that displays: 14:14-16:00, or are there two different time fields, a start field and an end field, or is there one time field and whether it is a start or end time is determined by a separate field? In other words, we need the datatype of the actual field(s) and to know which ones you are working with...

-LB

 

[sorchard2000]

Ibass:
Currently, the data field is a number (not a string) that displays total number of minutes for that time period. It looks like:

example:
Total # cases | Total minutes 7a-3p | Total minutes 3p-7p|
5 | 321 | 139 |

One of the problems is that when figuring up the total minutes for 7a-3p, the sum is calculated for all cases that START before 3pm (regardless of when it ends.) So, the sum can, at times, be inaccurate because of this.
Sorry, I didn't give a better explanation-I'm a Crystal newbie.
thanks for your patience.
sorchard2000

 

[lbass]

Please reread my post. I was asking about the time field(s) that are used to generate the summaries, not the summaries themselves.

At the detail level, you must have something like:

start end minutes
7:15 14:15 420
9:30 10:30 60

Or maybe it looks like:

shift minutes
9:15 - 17:15 480

Or maybe it looks like this:

time type minutes
9:15 start
17:15 end 480

I am asking about the start/end or shift times--what datatype are they, and are there two fields or one.

-LB

 

[sorchard2000]

Sorry, misunderstood.
It looks like:
7-3p Start time minutes
9:15:00AM 52
11:15:00AM 126

Does that answer your question?

sorchard2000

 

[lbass]

Try something like this:

//{@7to3mins}:
if {table.starttime} in time(07,00,00) to time(15,00,00) then
if {table.starttime} + ({table.minutes}*60) > time(15,00,00) then (time(15,00,00) - {table.starttime})/60 else
if {table.starttime} + ({table.minutes}*60) <= time(15,00,00) then {table.minutes}

//{@3to7mins}:
if {table.starttime} in time(07,00,00) to time(15,00,00) and
{table.starttime} + ({table.minutes}*60) > time(15,00,00) then (({table.starttime}+{table.minutes}*60) - time(15,00,00))/60 else
if {table.starttime} in time(15,00,01) to time(19,00,00) then
if {table.starttime} + ({table.minutes}*60) > time(19,00,00) then (time(19,00,00) - {table.starttime})/60 else
if {table.starttime} + ({table.minutes}*60) <= time(19,00,00) then {table.minutes}

//{@7to11mins}:
if {table.starttime} in time(15,00,01) to time(19,00,00) and
{table.starttime} + ({table.minutes}*60) > time(19,00,00) then (({table.starttime}+{table.minutes}*60) - time(19,00,00))/60 else
if {table.starttime} in time(19,00,01) to time(23,00,00) then
if {table.starttime} + ({table.minutes}*60) > time(23,00,00) then (time(23,00,00) - {table.starttime})/60 else
if {table.starttime} + ({table.minutes}*60) <= time(23,00,00) then {table.minutes}

Don't have time to test this out much, but maybe it will get you started.

-LB

 

[sorchard2000]

1bass:
Thanks so much for your help! I will try to test it soon!

Thanks again!

sorchard2000