business hours 1

[dmccallum]

I loved the FAQ by K. Hamady, especially the number of "work days". It was exactly what I needed, almost. I also need to track help calls returned by work hours of 8 am to 5 pm. If someone receives a help call at 4 p.m. Monday and returns the call at 9 a.m. Tuesday, that would be 2 hours. Is there a formula that I could combine with the "work days" that not only considers weekend and holidays but also business hours?

If so, it should also be added to the FAQ, IMHO.

 

[kenhamady]

This has been discussed and if you do a keyword search in the general forum you will find at least one description (by me) of how to convert business days to business hours.

The reason that I haven't added this to my FAQ is because not everyone has the same data type on their time inputs. What data type is your time input?

Ken Hamady

http://www.kenhamady.com/

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

 

[dmccallum]

the date is text
the time numeric

 

[kenhamady]

You mean that the time is a separate column, with a numeric data type? Like with a decimal point? That is odd. What do 9:45am and 9:45pm look like as numerics? Ken Hamady

http://www.kenhamady.com/

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

 

[dmccallum]

No, sorry I wasn't more clear.

The beginning date is one column such as 03/14/01 and the beginning time is another column such as 8:15. There is no need for am and pm since work hours are 8:00 - 5:00 M-F.

The ending date and time is an identical format.

 

[kenhamady]

That means that it is either a character field or a time field, but we need to know which. Place the time fields onto a report, right click on each, and tell me which data type Crystal shows at the top of the browse window.

Ken Hamady

http://www.kenhamady.com/

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

 

[dmccallum]

No, sorry I wasn't more clear.

The beginning date is one column such as 03/14/01 and the beginning time is another column such as 8:15. There is no need for am and pm since work hours are 8:00 - 5:00 M-F.

The ending date and time is an identical format.

 

[kenhamady]

I guess I am not being clear. In databases, every column in every table is assigned a "data type". There are variations, but most fall into one of the basic four:

Numeric
Date/DateTime
Character
Boolean.

Your time field is either a character field or a DateTime with only the time showing. I can't help you write the formula unless you tell me which type it is. My last post explains how to find out. Ken Hamady

http://www.kenhamady.com/

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

 

[dmccallum]

Sorry to take so long but I think I have the information you need now.

Time
String
Length: 8

Date
String
Length: 10

Thanks for your patience

 

[kenhamady]

I have added formula 13 to the FAQ. It should get you close. I assume that you were able to modify the DATE formula to work with your STRING dates. Ken Hamady

http://www.kenhamady.com/

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

 

[dmccallum]

Both formulas work exactly like I needed. Thank you so much for all that work.

But. . .

I can't group on the formulas. They don't even appear on the drop down list.

Why is that and how can you get around it?

 

[kenhamady]

That is because these are both "whilePrintingRecords" formulas.
If you change them to "WhileReadingRecords" then they will be available for grouping. As long as your inputs are data fields, you can do this. Ken Hamady

http://www.kenhamady.com/

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

 

[dmccallum]

I don't understand; what determines if they are data fields?

 

[dmccallum]

And what is the difference between whileprinting and whilereading in regard to these two formulas?

 

[MalcolmW]

In this case, the more precise definition is whether the objects are evaluated during the reading of the reading of the report records.
The simplest way to determine whether a formula can indeed be evaluated during the reading of the records is to force it to be evaluated then, by beginning the formula with the line
WhileReadingRecords ;
If this generates an error message when running the report, or incorrect or no values show up, then the object needs to be evaluated later, using
WhilePrintingRecords ;
For more information, the concept of evaluation time is fully documented in Crystal Reports help. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[kenhamady]

By data fields, I meant fields coming directly from the tables, as opposed to using calculated dates formulas.

To get a good understanding of evaluation times go to the back of the CR users guide and read the appendix entitled "a report processing model". Ken Hamady

http://www.kenhamady.com/

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