Problems with dates. (1 Viewer)

[Gethyn]

Hi everyone,

I'm fairly new to using Crystal Reports, version I'm using is 8.5.

I have a report that I have done tracking all the sick days taken by employees between dates specified by a parameter field.

The report is grouped by Region then Month and Year then Department then employee.

The report is working fine except for two problems;

1) The report is ignoring weekends, so for example if an employee is off sick from a Thursday and goes back to work on a Tuesday the report is showing him as sick for 5 days instead of three. This is the formula I currently have;

if {ABSENCE.UNTILDATE}={ABSENCE.FROMDATE}then 1 else round({ABSENCE.UNTILDATE}-{ABSENCE.FROMDATE})+1

2) IF the absense date crosses over two months i.e. runs from end of Jan to beginning of Feb, the whole period is shown in the Jan section. I would like it to split at the month end and show the last part in Feb.

Hope sombody can help.

Thanks,

Gethyn.

 

[Karen Howard]

For your first problem you should look at using the DayOfWeek function. This will allow you to use a loop to calculate the number of days as long as the weekday is Monday to Friday. You can then not increment the count if the day is Saturday or Sunday. Try using DayOfWeek(datefield, crMonday). The crMonday is an inbuilt constant which will make Monday day 1 of the week, Your loop can then test to ensure that the count is only updated if the the dayofweek is <= 5.

For the second problem, you need to also use a loop and calculate the number of days in one month and, separately, the number of days in the next month.

You might want to look up how to create a function to do these calculations as you might find them usful in other reports. If you have a Crystal Repository you can store the functions in there and then easily retrieve them whenever you need to use them.

Hope all this helps.