Working hours, overHours from beetwen beginTime to endTime

[amasny]

I have Begin Time and End Time in my database table.
I would like to group hours to type 1 (08:30-17:00),
type 2 (17:00-21:00), type 3 (21:00-8:30).
I can to this if someone enter for example :
once 08:30-17:00 (so it will be 8h30min of 1 type)
and next 17:00-20:00 (so it will be 3h of 21 type)

But i can't extract it if someone enter his day task this way :

8:30-21:00 - so it will be 1 and 2 type hours.
How to extract this in formula for display ?

 

[kenhamady]

This is probably 2 complex to deal with in TT but I will try to point you in the right direction.

First, you would need to do 2 separate calculations for each record. One for each type. Some Questions:

Can the times be more than 24 hours? What is the maximum amount of time? Is there also a start and end date?


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[amasny]

Thanks for reply Instructor.
Working hours must be inside one day from 00:01 to 23:59
so time must be less than 24 hours and lay inside one day.
There is also start and end date.

Regards Arek Masny

 

[kenhamady]

I think you missed my question. Are the Start Date and End Date always on the same day? How far apart can they be?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[amasny]

Sorry KenHamady.
The Start Date and End Date are always on the same day - they must be.
Regards Arek

 

[kenhamady]

That makes things a bit simpler.

The general approach is to calculate the total time.
Then calculate the business time.
Then subtract one from the other.

First write 2 formulas that convert your Time fields to a true time values. Probably using the TimeValue() function.

Then write a Total Time subtracting one formula from the other:
{@RealEnd} - {@RealStart}

Then write a formula @BusStart:

If {@RealStart} < TimeValue (&quot;08:30:00&quot
then TimeValue (&quot;08:30:00&quot
else

If {@RealStart} > TimeValue (&quot;17:00:00&quot
then TimeValue (&quot;17:00:00&quot
else {@RealStart}

Write a formula @BusEnd:

If {@RealEnd} > TimeValue (&quot;17:00:00&quot
then TimeValue (&quot;17:00:00&quot
else

If {@RealEnd} < TimeValue (&quot;08:30:00&quot
then TimeValue (&quot;08:30:00&quot
else {@RealEnd}

Now you can do a formula for {@BusTime}

{@BusEnd} - {@BusStart}

This last is one of the time formulas you need.
Subtract this form the Total Time above to get the other time formula you need.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[amasny]

Thank's. Your advice helped me very much.
But one think i can't achieve.
I would like to display my results this way :

hours Type Time
1 180.5
2 55
3 24

When hours type as said at the beginning :
1 - 8.30 - 17.00
2 - 17.00 - 21.00
3 - 21.00 - 8.30

How may i do that. Now I know how i can calculate time, but how i can display it in proper form.


Regards Arek

 

[kenhamady]

What do the values look like now?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[amasny]

Generally i understand your answer but i don't know how use it to have proper result.
I'm beginner in Crystal Reports so i'm not so clever yet in this area.


Below i've described you how my previous solution worked :

Up to this time i've had one formula @hoursType which computes type of hours and looks as following:

dim hourType as string

if {ACTIONS.BEGIN HOUR}>=timevalue(8,30,0) and {ACTIONS.END HOUR}<=timevalue(17,0,0) then
hourtype=&quot;1&quot;
elseif {ACTIONS.BEGIN HOUR}>=timevalue(17,0,0) and {ACTIONS.END HOUR}<=timevalue(21,0,0) then
hourtype=&quot;2&quot;
elseif ({ACTIONS.BEGIN HOUR}>=timevalue(21,0,0) and {ACTIONS.END HOUR}<=timevalue(23,59,59)) or ({ACTIONS.BEGIN HOUR}>=timevalue(0,0,0) and {ACTIONS.END HOUR}<=timevalue(8,30,0)) then
hourtype=&quot;3&quot;
end if
if dayofweek({ACTIONS.BEGIN DATE})=7 and dayofweek({ACTIONS.END DATE})=7 then
hourtype=&quot;3&quot;
end if
formula=hourtype



and second formula @dateDiff which computes work hours and looks like that :


dim daysdiff as number
dim DateStart as datetime
dim dateEnd as datetime


datestart=datetimevalue({ACTIONS.BEGIN DATE},{ACTIONS.BEGIN HOUR})
dateend=datetimevalue({ACTIONS.END DATE},{ACTIONS.END HOUR})
daysdiff=(DateDiff(&quot;s&quot;,datestart,dateend)/3600)

formula=DaysDiff




,I've grouped my report by @hoursType formula
and i 've got results in this form :

hoursType sum of @dateDiff
30
1 150
2 20
3 15

blank field of hoursType is result of inserting hours like i described :

8:30-20:00

which are not inside working hours of particular type.

 

[kenhamady]

What is Hours Type?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[amasny]

&quot;Hours type&quot; describes type of working hours by following
rules:

Hours Type Hours Span
1 : 8.30 - 17.00
2 : 17.00 - 21.00
3 : 21.00 - 8.30

Best Regards Arkadiusz Masny

 

[kenhamady]

Sorry, somehow I missed that there were 3 types. I was trying divide into 2 types (business hours and non-business hours). What you would need is a bit more complex.

If you total the formula {@BusTime} it will give you type 1. Add two more formulas called {@2Start} and {@2End} which are similar to {@BusStart} and {@BusEnd} but use the actual start and end time numbers for type 2. Then you can do {@2Time} which is {@2End} - {@2Start}.

Then you subtract {@BusTime} and {@2Time} from {@RealTime} to get type 3 or {@3Time}. What you want are totals of {BusTime}, {@2Time} and {@3Time}.



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com