elapsed times based on conditons

[cmpgeek]

morning yall...

i am trying to create a formula to tell me the elapsed time of a case; but based on a certain condition...

the report itself is a room utilization report. because of that, if the beginning or the end of the case is outside of the parameters chosen, we only want it to report on the times within the parameters.

EX Case X starts at 0738 and ends @ 0902.
ordinarly the elapsed time would be 84m,
however - we are looking for room util from 0800 -1630.
so i want it to "ignore" everything from 0738 - 0800.

as it stands right now, crystal is still giving me 84 mins, but i cant figure out why...

here are the formulas i am using:

//if 1 then case falls within time prompts, if 0 then it doesn't

if {case.CAS_ROOMSTA} in totext({?starttime}) to totext({?stop time})
or {case.CAS_ROOMSTP} in totext({?starttime}) to totext({?stop time})
then
1

else
0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


if {@time flag} = 1
then
if not ({case.CAS_ROOMSTA} in totext({?starttime}) to totext({?stop time}))
and {case.CAS_ROOMSTP} in totext({?starttime}) to totext({?stop time})
then
elapsedtime(totext({?starttime}),{case.CAS_ROOMSTP})

else if {case.CAS_ROOMSTA} in totext({?starttime}) to totext({?stop time})
and not ({case.CAS_ROOMSTP} in totext({?starttime}) to totext({?stop time}))
then
elapsedtime({case.CAS_ROOMSTA},totext({?stop time}))

else if {case.CAS_ROOMSTA} in totext({?starttime}) to totext({?stop time})
and ({case.CAS_ROOMSTP} in totext({?starttime}) to totext({?stop time}))
then
elapsedtime({case.CAS_ROOMSTA},{case.CAS_ROOMSTP})

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

can anyone find the error in one of these?
any help would be greatly appreciated...

 

[cmpgeek]

i just noticed something else...

the formula works when the case is started within the time frame and finished after...

EX case started at 1545 / closed at 1745
just based on the numbers it should show 2hrs
but since the formula seems to work on those ones, it comes back with 45 mins...

(i think i am getting more confused now lol)

 

[resdevlabs]

I downloaded a white paper and a UFL file from Crystal Decisions.

cr_business_days_hours.zip
BusinessDaysUFL.zip

These files came with very good documentation and samples. You should be able to modify their code to meet your needs.

Rob