Converting number into hour minutes 1

[newbie0423]

Hello Experts,

I have a report that is showing the time a nurse went into a room and the time the nurse came out of the rooom. I am using a formula to get the total minutes the nurse was actually in the room.
@nursedifftime
DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@nurseintime}),DateTime(CDate ("November 10, 1999"),{@nurseouttime}) )
and then I'm doing a count on the total minutes per week the nurse spends in each room

Nurse A IN Room Out Room total time
8:00 8:30 30
9:00 10:00 60
10:30 11:00 30
11:15 12:00 45
Total 165 would like this to say 2 hours and 45 mins
How can this be accomplished? I have tried to follow some of the previous post. but I have not found one that deals with times in this way.

 

[fisheromacse]

I do not have crystal in front of me, but think something like below will meet your needs.
apologies in advance for any misunderstandings or errors.


//{@Hours}
floor({@nursedifftime}/60)

//{@Minutes}
remainder({@nursedifftime}/60)

//{@Combined}
ToText({@Hours},0) & " Hours and " & ToText({@Minutes},0) & " mins

 

[HowardHammerman]

Here is how I would do it.

numbervar minutes := DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@nurseintime}),DateTime(CDate ("November 10, 1999"),{@nurseouttime}) );
numbervar hours := Int (minutes/165);
numbervar remminutes := minutes - (hours*60);
totext(hours,0,"")+" hours "+ totext(remminutes,0,"") +" minutes"

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[newbie0423]

So I tried them both and they do not give me the correct answer.
fisheromacse i got an error message with your telling me that remainder({@nursedifftime}/60) not enough arguements have been given to this function

and the second solution just gives me incorrect minutes with no hours.

 

[HowardHammerman]

Try this:

numbervar minutes := DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@nurseintime}),DateTime(CDate ("November 10, 1999"),{@nurseouttime}) 
numbervar hours := Int (minutes/60);
numbervar remminutes := minutes - (hours*60);
totext(hours,0,"")+" hours "+ totext(remminutes,0,"")+" minutes";

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[IanWaterman]

Remander uses a , not /

Try a combination of the two solutions

Whileprintingrecords;

numbervar minutes := DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@nurseintime}),DateTime(CDate ("November 10, 1999"),{@nurseouttime}) );
numbervar hours := Int (minutes/60);
numbervar remminutes := remainder(minutes, 60);

totext(hours,0,"")+" hours "+ totext(remminutes,0,"") +" minutes"

Ian

 

[newbie0423]

I'm sorry but it's still not computing the numbers correctly. For example, I have a total of 507 minutes and using the formulas it comes back with a total of 2 hours and 28 minutes. For some strange reason it looks like it's totalling the last number in column of numbers.
Example
355
150
124
148
I got a total of 2 hour and 28 minutes, which is equal to the last number 148. Most of the columns are using the last number in the column to calculate the hours and minutes.

 

[IanWaterman]

In your example where are the fields placed, are they details or summaries.

The figures you list do not sum to 507.

Now not sure what you are trying to achieve

Ian

 

[HowardHammerman]

Now I see. We had all thought that the time was on one record. Of course you are getting the last record. You are obviously grouping on some field, I believe it is the nurse's ID. So you will have to sum up the minutes and then use that sum in the formula. Create a fomula field to calculate minutes using the expression you already have:

// formula field calminutes;
DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@nurseintime}),DateTime(CDate ("November 10, 1999"),{@nurseouttime})

Place that formula field in the details section and sum the minutes to the group defined by the nurse ID field.
Then create another formula like this:

numbervar hours := Int (@calminutes/60);
numbervar remminutes := minutes - (hours*60);
totext(hours,0,"")+" hours "+ totext(remminutes,0,"")+" minutes";

Place the second formula in your group footer.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[newbie0423]

Hi Ian,
I'm sorry I'm having a hard time explaining. I have a list of times. Nurse in room to nurse out of room I would like to have the total number of minutes converted to hours and minutes.

Example
In Out total time
7:43 11:56 253
13:30 15:16 106
10:12 12:40 148
total 507 (I'm using my nursediff formula here to get the toatal)
The formula that you gave me seems to be calculating the last number in the total time column, not the total itself. I got a total of 2 hours and 48 minutes.

Example 2

In OUt total time
7:40 10:40 180
11:38 14:25 167
11:20 13:10 110
total 457
I'm showing 1 hour and 50 minutes which is equal to the 110 minutes the last number in the column.

 

[HowardHammerman]

Whoops,another error.
The second formula should read:

numbervar hours := Int (sum({@calminutes},{table.NurseID})/60);
numbervar remminutes := minutes - (hours*60);
totext(hours,0,"")+" hours "+ totext(remminutes,0,"")+" minutes";

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[newbie0423]

Hi Howard,
I'm getting a message saying that a number, currency,amount,boolean,date, time, date-time or string is expected here.
The minutes is highlighted right before the - sign

 

[HowardHammerman]

change the word "numbervar" to "currencyvar" in all instances in the formula.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[newbie0423]

currencyvar put a $ in front of the numbers that returned.

 

[HowardHammerman]

That is very strange.

I am out of ideas.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[newbie0423]

Thanks for your time!

 

[pmax9999]

My recommended approach is along the lines of that proposed by fisheromacse, but using the syntax corrections provided by IanWaterman.

The reason you are getting hours and minutes of the final record rather than the total minutes is that the amounts need to be summed first.

ToText(Truncate(Sum({@nursedifftime})/60), '#') + ' Hours and ' + ToText(Remainder(Sum({@nursedifftime}),60), '#') + ' minutes'

If the report is grouped by nurse, the "Sum({@nursedifftime})" in the formula would need to be amended to "Sum({@nursedifftime},{Table.YourNurseGroupField})"

Cheers
Pete

 

[newbie0423]

Hi Pete,
Thanks for the information. I revised my @nursetimediff as you suggested. I then when I try to use your code, I get an error message saying that 'this field can not be summarized'

 

[pmax9999]

My solution involved a new formula using a sum of your existing @nursetimediff forumula. Please explain what you did and post the contents of your formula.

 

[IanWaterman]

Sounds like your nursetime formula is using printtime functions and can not be summarised.

Create 3 new formual

@reset// place this in nurse group header

Whileprintingrecords;
global numbervar nursetime:=0

@Eval//plac this in details or whereever you are currently showing nurse time for each visit
Whileprintingrecords;
global numbervar nursetime:=nursetime+@nursedifftime;

@Total place this in nurse group footter
Whileprintingrecords;

numbervar minutes := global numbervar nursetime;
numbervar hours := Int (minutes/60);
numbervar remminutes := remainder(minutes, 60);

totext(hours,0,"")+" hours "+ totext(remminutes,0,"") +" minutes"

Ian