Trying to display total minutes after 1600 for a report 1

[mvalley]

I have been asked to create a report that will display how many minutes after a certain time a case is being done. I think this requires either an if then else formula or 2 different formulas.

Example:
pat_in_room field time result is 1602
pat_out_room field time result is 1751

I have been asked to calculate the total minutes for the case done after 4pm, which in this example would be 109 minutes

2nd part of this equation would be
pat_in_room field time result is 1416
pat_out_room field time result is 1649 which means I am looking for the result to be 49 minutes

I am truly lost on how to go about doing this and would welcome any guidance

 

[briangriffin]

Try this:

if {In time} < time(16,00,00) then

({Out time} - time(16,00,00))/60

else

({Out time} - {In time})/60

 

[MCuthill]

mvalley,

I would suggest swapping your start time for 1600 if less than 1600. Then perform your math on this formula field instead of the datafield.

{@StartTime_Modified}

[blue]IF[/blue] {Table.Pat_In_Room} < 1600 [blue]THEN[/blue] 1600 [blue]ELSE[/blue] {Table.Pat_In_Room}

{@TimeAfter1600} -- should provide the time after 1600.

{Table.Pat_Out_Room} - {@StartTime_Modified}

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."

 

[mvalley]

Mike, you solution worked best for me, however I need to format the results or change the way they are displayed.
Example: result is 104 which I need to display as either 1hr 04 mins (1:04) or 64 mins. Can you help me again. Thank you also Brian for your suggestion.

 

[MCuthill]

mvalley,

I have tried some quick testing on this, and it worked for my samples. I am assuming that your datafield would work as follows if times were in sequence:

... 1558, 1559, 1600, 1601 ...
NOT: ... 1558, 1559, 1560, 1561 ...
(I assume it would be the first sequence of numbers)

The following Formula fields should work, either in addition to the above, or it could be incorporated. Both Solutions as follows (I personally like keeping everything in separate formulas incase just one "step" gets changed or if that item needs used elsewhere, but either will work).

Option #1: Add another new formula as follows:
{@Format_TimeAfter1600}

[blue]WhilePrintingRecords[/blue];
[blue]Shared NumberVar[/blue] TotalTime;
[blue]Shared NumberVar[/blue] Hours;
[blue]Shared NumberVar[/blue] Minutes;

TotalTime:={@TimeAfter1600};

Hours:=[blue]Int[/blue](TotalTime/100);
Minutes:=TotalTime-(Hours*100);

[blue]IF[/blue] Hours = 0 [blue]THEN[/blue]
    [blue]ToText[/blue](Minutes,"00") & " mins"
[blue]ELSE[/blue]
    [blue]ToText[/blue](Hours,"0") & " hrs " & [blue]ToText[/blue](Minutes,"00") & " mins";

Option #2: Change Existing {@TimeAfter1600} as follows:
{@TimeAfter1600}

[blue]WhilePrintingRecords[/blue];
[blue]Shared NumberVar[/blue] TotalTime;
[blue]Shared NumberVar[/blue] Hours;
[blue]Shared NumberVar[/blue] Minutes;

TotalTime:={Table.Pat_Out_Room} - {@StartTime_Modified};

Hours:=[blue]Int[/blue](TotalTime/100);
Minutes:=TotalTime-(Hours*100);

[blue]IF[/blue] Hours = 0 [blue]THEN[/blue]
    [blue]ToText[/blue](Minutes,"00") & " mins"
[blue]ELSE[/blue]
    [blue]ToText[/blue](Hours,"0") & " hrs " & [blue]ToText[/blue](Minutes,"00") & " mins";

The formula may require some fine tuning (limited testing), please advise as to your findings when implemented with your data.

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."

 

[mvalley]

Mike, I tried both options with the same results. 95% accurate.
issue with a few records. Yes, the numbers are 1657, 1658, 1659, 1700, 1701 etc. Some errors are
ex: in 1941 out 2011 comes up as 70 mins (should be 30 mins)
in 1812 out 2110 comes out as 2 hrs 98 mins vs 2 hrs 58 mins
in 1712 out 1801 comes out 89 vs. 49

I'll keep playing with it, but if you have any suggestions I would appreciate it. Thanks again, Mary

 

[MCuthill]

Hi Mary,

I was concerned about that, I had a rough formula I was trying yesterday to fix this (thought it may be an issue). I will try it again and use some of your samples.

A quick solution "may" be that if the minutes is greater than 59, then minutes:=minutes=40; (the difference between 100 and 60).

I will advise as to my findings.

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."

 

[MCuthill]

Hello Again Mary,

In testing the shown scenarios, the adjustment to minutes seems to be the most simple solution.

{@Format_TimeAfter1600}

[blue]WhilePrintingRecords[/blue];
[blue]Shared NumberVar[/blue] TotalTime;
[blue]Shared NumberVar[/blue] Hours;
[blue]Shared NumberVar[/blue] Minutes;

TotalTime:={@TimeAfter1600};

Hours:=[blue]Int[/blue](TotalTime/100);
Minutes:=TotalTime-(Hours*100);

[blue]IF[/blue] Minutes > 59 [blue]THEN[/blue]
    Minutes:=Minutes-40
[blue]ELSE[/blue]
    Minutes;

[blue]IF[/blue] Hours = 0 [blue]THEN[/blue]
    [blue]ToText[/blue](Minutes,"00") & " mins"
[blue]ELSE[/blue]
    [blue]ToText[/blue](Hours,"0") & " hrs " & [blue]ToText[/blue](Minutes,"00") & " mins";

As in my original posting of this formatting, you can either reference {@TimeAfter1600} or do the calculation in this formula - the result will be the same either way.

Hope this helps, 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."

 

[mvalley]

Thank you, Thank you, THANK YOU. Works like a charm. Administration is VERY happy. I appreciate all your help with this Mike. Have a great day. Mary

 

[MCuthill]

You are most welcome Mary, happy to help!

Have yourself a terrific Tuesday!

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."