×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Finding elapsed time on site

Finding elapsed time on site

Finding elapsed time on site

(OP)
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.

RE: Finding elapsed time on site

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)

RE: Finding elapsed time on site

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

-Jeff

RE: Finding elapsed time on site

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)

RE: Finding elapsed time on site

(OP)
Hi Ken,

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

RE: Finding elapsed time on site

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)

RE: Finding elapsed time on site

(OP)
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

RE: Finding elapsed time on site

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)

RE: Finding elapsed time on site

(OP)
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

RE: Finding elapsed time on site

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close