Finding elapsed time on site

[zircom]

I need to find the total elapsed time between dates and times, report shows:

1/2/00 12:00 7/2/00 14:20

I need to take into account:

Weekends & Holidays

Working day 08:30 - 17:30

Any help with this will be most welcome.

 

[kenhamady]

See the FAQ 1.B for a formula that finds working days between two dates. It does holidays and weekends.

You will have to adjust for the hours each day, and then adjust the beginning and end day for start and stop time. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jschodde]

Ken,
Where is FAQ 1.B? I don't see it on this forum. Which one are you referring to?

-Jeff

 

[kenhamady]

Sorry, I am not used to the split forums yet.
It is FAQ 1.B in the CR General forum. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[zircom]

Hi Ken,

Thanks for the reply, however I was wondering how to go about the times part (I think it is me being thick).

 

[kenhamady]

Have you gotten the days to work? You have to do that first.

Do you have 2 fields or 4?
What data types are each of these? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[zircom]

I have got the days to work fine (changed holidays etc).

The time field is 14:33 for example.

Hope this helps, your fast response is very helpful indee

 

[kenhamady]

That doesn't tell me the data type. Preview the report, right click on the value and select "Browse Field Value". At the top of the window it will probably say either DateTime or Character.

This is a separate field from the data, right? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[zircom]

Thanks again for the prompt reply.

The datatype is 'string'(5 characters)

If you mean a seperate field from the 'date' then yes it is.


Thanks

 

[kenhamady]

First if you multiply the full days by the number of minutes in each work day to get the a base number.

Then, you have to adjust for the first and last day based on the two times. Split the time fields into hours and minutes and then convert to a single minutes elapsed value. This allows you to calculate the elapsed time from the start of the workday on the first and last day. These elapsed times can be used to adjust the total from the full days above.

To convert the hours to minutes you use:
Val({time} 1 to 2 ) *60

Extracing the minutes is:
Val({time} 4 to 5 )


When you are done and have a net minutes, you can convert back to an hours/minutes value Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)