Need to sum hours from calculated elapsed time field that is a string 1

[rhartigan]

I have a string field that is Computed Time showing for example, 02:30. I have tried converting a number of different ways and even using the primary fields of Date Left and Date Returned to calculate the number of hours between but I can't seem to get the summation correct when adding 02:30 and 02:30 together because of the rounding on the hours. I'm able to display it as a string but not as numeric. Any suggestions would be most helpful.

Thanks in advance.

 

[xxyyzz49]

Try TIMESERIAL(hh,mm,ss) function. TIMESERIAL(2,30,00) for 2:30 AM or TIMESERIAL(14,30,00) for 2:30 PM. The help feature with CR gives several more interesting examples.

 

[SkipVought]

hi,

Time DURATION as STRING for calculations???

02:30 is 2 hours, 30 minutes, correct? You're gonna havta convert stuff. What is the basis for your duration UNITS, DAYS, HOURS, MINUTES, SECONDS? Whatever units, then the 2 hours needs to be converted to that UNIT and the 30 minutes needs to be converted to that UNIT, and the 2 summed. I think we did that in 6th grade.

But really, your Time Durations ought to be real time values that you can do arithmetic on directly, but displayed as hh:mm.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

@pmsawyer, the OP is referring to ELAPSED TIME or DURATION.

Can you use the TimeSerial() function if the duration exceeds 23:59:59 duration?

The other thing to note using the TimeSerial() function, is the the function returns DAYS as unit. So if the user needs to work in or display HOURS, then DAYS must be converted to HOURS (DAYS * 24)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[xxyyzz49]

TIMESERIAL() returns a time value, DATESERIAL returns a date value.

I missed the elapsed time inference but no matter. Use TIMESERIAL() to compute the sum or difference for you. See the examples in the help file. But for a quick example TIMESERIAL(12 + 2,00,00) returns 2:00:00 PM TIMESERIAL(12,120,00) returns 2:00:00 PM, try it yourself. Create the formula and drop it in a header or footer to display. The first adds 2 hours to noon and the second add 120 minutes to noon.

Another example: TIMESERIAL(12 + 2, 15 + 30,0) returns 2:45:00 PM. Add 2 hours and 30 minutes to 12:15PM. Break out the units plug them in the correct position and the answer emerges.

In my sixth grade we had manual adding machines [link 100 key add machine]

http://www.google.com/url?sa=i&sour...mqbiN7tm5y9raUsX-c9bNQwQ&ust=1387553187636270

[/url]

 

[SkipVought]

But what about a duration of 48:00, for instance? TimeSerial would return 2 days, and the Time format would be 00:00.

Bottom line: you have to account for elapsed time that exceeds 24 hours.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rhartigan]

Thanks for the help.

I looked at TimeSerial but I am trying to do a summary for a group and beyond the 24 hour issue it doesn't seem to be the way I need to go. Literally, the record is ComputedTime=02:30 and I'm trying to sum the computed time for the group. So, I went back to my 6th grade math as you said.

So, I'm using the datetime fields to calculate the number of seconds and try to convert it to the number of hours using a decimal. So, 2:30 is 2.5 hours. Then adding 2.5 hours + 3 hours to get 5.5 hours. It's not the math I have a difficult time with it is the formulas and using them efficiently. However, what I get with this formula is not able to have a summary done on it. So, my limited crystal skills are failing me.

WhilePrintingRecords;
NumberVar TotalSec := DateDiff('s',{Date_Left},{Date_Return});
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
//Convert minutes to hours to add them together//
NumberVar MinHrs := (Minutes/60);
NumberVar TotalHours := Hours + MinHrs;

TotalHours;

-Rebecca

 

[xxyyzz49]

TIMESERIAL returns times. 12:00:00 + 48 hours returns 12:00:00

Here is an example formula for converting interval to minutes using your record for 2.5r hours. So where this formula uses the variable elaptime you would use the data field name.

// {@add0230}
local stringvar elaptime := "ComputedTime=02:30";
local numbervar hh;
local numbervar mm;

hh := tonumber(extractstring(elaptime,"=",":"));
mm := tonumber(extractstring(elaptime,":","z"));

(hh*60) + mm

Then to use the results of the above formula the following example

dateadd("n",{@add0230},currentdatetime )

This returns the currentdatetime, use your start date time, plus the number of minutes from the formula example. It works across intervals of days or minutes. Note that built in functions are used where possible and all the calculations are straightforward avoiding sixth grade math where possible.

 

[pmax9999]

I am a bit confused about what you have and what you are trying to achieve here. From my reading, you have times stored as strings that you are trying to sum. Assuming my understanding is correct (and it is possible I am way off here), I would do it this way:

1. Convert the Time strings to hours (in decimals) using the following formula (2:30 becomes 2.5):

[Code {@Time-Hours}]
Val(Split({Table.Time}, ':')[1]) + (Val(Split({Table.Time}, ':')[2])/60)
[/Code]

2. Use a simple Summary to total the result of that formula

3. To return the Total Hours from decimals to a string use the following formula:

WhilePrintingRecords;
NumberVar T := Sum({@Time-Hours});
NumberVar H := Truncate(T);
NumberVar M := (T-H)*60;

ToText(H, '#') + ':' + ToText(M,'#')

Does this help.

If not, it would be beneficial if you pasted an example showing real data and the expected result.

Cheers
Pete

 

[xxyyzz49]

Pete

Rebecca wrote earlier:

Literally, the record is ComputedTime=02:30 and I'm trying to sum the computed time for the group.

Your formula works on the interval nicely but not the data as she says it is. As for what she is trying to get done there are at least two ways of reading her post.

Rebecca,

If there is no solution posted above may I respectfully ask that you restate your problem a bit more clearly. PMAX9999 and the others have posted excellent responses in the past and no doubt can supply an answer to your problem once it is understood correctly.

Regards
Pete

 

[rhartigan]

Wow, thanks everyone.

So, I apologize if this was confusing. What sounds perfectly simple in my head is of course difficult to translate into understandable English. All I really needed was accomplished in Step 1 of what Pete posted up above. I just needed the values in order to convert to a number and then do a sum for a group. Formula writing and crystal syntax are definitely my weaker points so that helped tremendously.

Thanks again,

Rebecca

 

[BettyJ]

I tried Pete's solution by adding three variables in string format and it is working fine.

Make sure to format @Time-Hours and the second formula to accommodate decimals. I selected the style, -1123.0000.

eg: 12:30
2:20
5:20

The results are
12.5000
2.3333
5.3333

Total
20.1667

Total in time format
20:10

I like your simple formulas. Thanks Pete.