How to change 111 to reflect 71 minutes

[mvalley]

I have a formula to display cases started after 4pm,
@StartTime_modified which is
if{v_CRA_20_Billing_Summary.time_pat_in_room}<1600 then 1600
else{v_CRA_20_Billing_Summary.time_pat_in_room}

I have another formula called @Total Time which is
{v_CRA_20_Billing_Summary.time_pat_out_of_room}- {@StartTime_modified}

I am looking for results that will display the results in total minutes;
Example Time out is 1715 time in is 1604. Result is 111. I need the 111 to show 71 minutes

I really have problems working with formulas, and need all the guidance I can get step by step please.

I also need to be able to Sum these results for a total for the week. Can this be done as well?

 

[hilfy]

If cases started after 4 always end before midnight, this is not too difficult. The trick will be to convert the number that you have to a time.

I would change your @StartTime_modified formula to something like this:

if{v_CRA_20_Billing_Summary.time_pat_in_room}<1600 then TimeValue(1600/100/24)
else TimeValue({v_CRA_20_Billing_Summary.time_pat_in_room}/100/24)

Then create an end time formula to change the end number to a time using the same "/100/24" as above.

To calculate minutes, you would then do something like the following:

((Hour({@EndTime})*60)+Minute({@EndTime}) - ((Hour({@StartTime_modified)*60)+Minute({@StartTime_Modified}))

If the time can go past midnight, you'll need to split the calculation into two parts - the number of minutes between the start time and 11:59:59 (Crystal formula for this time is Time(11,59,59)) and the number of minutes between Midnight (TimeValue(1) and the actual end time. You can tell that you'll need to do this split calculation if the end time is less than the start time.

-Dell


A computer only does what you actually told it to do - not what you thought you told it to do.

 

[mvalley]

Followed you instructions, however the results returns 0 for all records. I'm afraid I'm stumped on this one. Mary

 

[hilfy]

Put the intermediate steps on the report and see what's being returned. I ran these formulas with actual numbers instead of fields and they worked. Is the data in the field a number or text? If it's text, you'll have to convert it to a number first.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[mvalley]

Thanks for your patiences with me Dell. Can you tell me what is the intermediate steps?

The {v_CRA_20_Billing_Summary.time_pat_in_room} and {v_CRA_20_Billing_Summary.time_pat_out_of_room} are both number fields.

Results display as follows:
Time in OR 1604 field is {v_CRA_20_Billing_Summary.time_pat_in_room}
Time out of OR 1715 field is {v_CRA_20_Billing_Summary.time_pat_out_of_room}

Total Time after 1600 67 (result should be 71) formula is ((Hour({@EndTime})*60)+Minute({@EndTime})) - ((Hour({@StartTime_modified})*60)+Minute({@StartTime_Modified}))

StartTime_modified formula is:
if{v_CRA_20_Billing_Summary.time_pat_in_room}<1600 then TimeValue(1600/100/24)
else TimeValue({v_CRA_20_Billing_Summary.time_pat_in_room}/100/24)

EndTime formula is:
if{v_CRA_20_Billing_Summary.time_pat_out_of_room}<1600 then TimeValue(1600/100/24)
else TimeValue({v_CRA_20_Billing_Summary.time_pat_out_of_room}/100/24)

All my results are off
In 1441 Out 1605 Total 3 (should be 5)
In 1453 Out 1614 Total 8 (should be 14)
In 1608 Out 1740 Total 80 (should be 92)

 

[MCuthill]

mvalley,

Just out on a limb here, and basing only off your original posting.

Perhaps something simple like...

IF {YourResult} > 60 THEN {YourResult}-40 ELSE {YourResult}

I know I used a similar solution for another TekTipper a few months back -- just can't recall the specifics to know if it would "for certain" apply here... but I think it should.

Perhaps worth a shot!

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Please convert your fields to actual times and then subtract, like this:

//{@TotalOutOfRoom}:
numbervar stno := {v_CRA_20_Billing_Summary.time_pat_in_room};
numbervar endno := {v_CRA_20_Billing_Summary.time_pat_in_room};
stringvar cst := totext(stno,"0000");
stringvar cend := totext(endno,"0000");
timevar st := time(val(left(cst,2)),val(right(cst,2)),0);
timevar end := time(val(left(cend,2)),val(right(cend,2)),0);
if st < time(16,0,0) then
st := time(16,0,0) else
st:=st;
datediff("n",datetime({table.indate},st),datetime({table.outdate},end))

You haven't explained whether the in and out times can span different days--if so, you MUST add in the "indate" and the "outdate" as shown above. If the times are ALWAYS within the same date, then you could just use currentdate to replace both in and out dates in the last line of the formula.

-LB

 

[mvalley]

Thank you everyone for your suggestions,the lst post worked the best for me. Thanks again