How to convert numbers in Crystal to hours.

[RArcega]

Hi,

I'm having issue to get the total number of hours work of the employee and if the employee is eligible to some allowances.

I have two columns in the table called Clock_in and Clock_out but the format is something like this
231000 (which is 11:10pm), etc.
I managed to resolved the issue on how to get the time format by using the formula below:

Clock_in:
numbervar Clock_INA := {CLOCK_IN};
stringvar CLOCK_INB := totext(Clock_INA,"000000");
time(val(left(CLOCK_INB,2)),val(mid(CLOCK_INB,3,2)),val(right(CLOCK_INB,2)))

Clock_out:
numbervar Clock_OutA := {CLOCK_OUT};
stringvar CLOCK_OutB := totext(Clock_OutA,"000000");
time(val(left(CLOCK_OutB,2)),val(mid(CLOCK_OutB,3,2)),val(right(CLOCK_OutB,2)))

My problem is when I'm doing the calculation to get the total number of work the result is NOT what I'm expecting..

eg1:
Clock_in: 190000
Clock_out: 230000
Total Number of work: 40000

eg2:
Clock_in: 093000
Clock_Out: 100000
Total Number of work: 170000

1. How can I convert 40000 to 4hrs? also how can I convert the 170000 to 1.5hrs?
2. How can I get the SUM of the Total Number of Work? (I'm trying to use the Summary (Insert -->SUmmary), but I cannot see the SUM option.

Appreciate your help!

 

[SkipVought]

Hi,

Ugly! Are you sure of your time in/out values and what they really mean?

If these values are what you say they are, then your arithmetic totally incorrect!

Let's take the second case. Here's what you claim you have...
[tt]
Clock_in: 09hrs 30min 00sec
Clock_Out: 10hrs 00min 00sec
[/tt]
The arithmetic you posted was 170000, but your intended arithmetic should have been 70000 or 7hrs 00min 00sec.
Well, you and I both know that the apparent duration is 30min.

I think you learned in third grade that you begin with seconds and if necessary, borrow a minute (60 seconds), then subtract minutes and if necessary, borrow an hour (60 minutes) then subtract hours.

Anyhow, what's the value in...
time(val(left(CLOCK_INB,2)),val(mid(CLOCK_INB,3,2)),val(right(CLOCK_INB,2)))
and...
time(val(left(CLOCK_OutB,2)),val(mid(CLOCK_OutB,3,2)),val(right(CLOCK_OutB,2)))
???
These are the two values that you ought to work with taking the difference.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[RArcega]

Thanks SkipVought for replying.

I managed to get the time format for both formulas (eg: 230000 the output for both is 11:00:00PM)
time(val(left(CLOCK_INB,2)),val(mid(CLOCK_INB,3,2)),val(right(CLOCK_INB,2)))
time(val(left(CLOCK_OutB,2)),val(mid(CLOCK_OutB,3,2)),val(right(CLOCK_OutB,2)))

so now how, can i subtract the Clock_in and Clock_out?

I used this formula to get the total number of hours work but the result is not right:
-------------------------
if {CLOCK_OUT} < {CLOCK_IN}
then ({CLOCK_OUT} + 240000) - {CLOCK_IN}
else {CLOCK_OUT} - {CLOCK_IN}
-------------------------

Note that I'm not a programmer hence my program might be ugly. I'm trying to put whatever knowledge I have but I think I'm already at the dead-end of the road hence I'm asking for help from experts.

Kindly assist.

Thanks,

 

[SkipVought]

Are you saying that
[tt]
Clock_in = time(val(left(CLOCK_INB,2)),val(mid(CLOCK_INB,3,2)),val(right(CLOCK_INB,2)))
Clock_out = time(val(left(CLOCK_OutB,2)),val(mid(CLOCK_OutB,3,2)),val(right(CLOCK_OutB,2)))
[/tt]
???

If Clock_in & Clock_out are TIME values, then I'd assume...
[tt]
If {CLOCK_OUT} < {CLOCK_IN}
Then ({CLOCK_OUT} + 1) - {CLOCK_IN}
else {CLOCK_OUT} - {CLOCK_IN}
[/tt]
...because I would think that the TIME value would be in units of DAYS.

So 23:00:00 or 230000 as a TIME value would be 23/24 = 0.958333 and adding 24 hours would be 24/24 = 1

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[lbass]

Try using the datediff function. If the times are always within the same date, you can use the following; otherwise, replace "currentdate" with your date field.

datediff("h",datetime(currentdate,time(9,30,0)),datetime(currentdate,time(10,0,0)))

Using your conversions, the formula would look like this (n=minutes so that you can get decimals when dividing by 60:

timevar CLOCK_IN;
timevar CLOCK_OUT:
//put in your number to time conversions here
datediff("n",datetime(currentdate,CLOCK_IN), datetime(currentdate,CLOCK_OUT))/60

-LB