Counting Weekdays Only?

[OnCrystal]

I was wondering if anyone had other ideas about how to accomplish this.

Totaling the days of two dates. {open_date/time} and {closed_date/time}.

However heres the catch... I know how to get the value that equal the total number of days open, but now how would you write a formula that will remove weekend days from this date range if the {open_date/time} and {closed_date/time} range spans a weekend or several weekends. Plus the {open_date/time} can be on a weekend day and the calculated range/time must then start on Monday 12:00am.

I have a complicated solution for all this but I'm not sure it's the best one and would like to know if anyone has a cleaner formula for this issue using Crystal 8.5.

-Thanks

 

[rosemaryl]

Dear OnCrystal:

Ken has a formula at his site that will do this:

http://www.kenhamady.com/formula.html

Work Days formula is what you are looking for. It will allow you to set working hours, etc.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

You can also just limit the rows in the database by adding the following to the record selection criteria:

dayofweek({MyTable.MyDate}) in 2 to 6

Now you have only the weekdays in the result set.

The best performance will be found on the database side, and one means for accomplishing this is to create a period table which might have all dates and a day type (date, daytype, dayname, holiday, workweek, month, quarter, etc.).

So you would join by date and add:
daytype = 'B' (for business)
and
holiday is null

There are many ways to design a period table, the point being that they can be extrememly useful.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear k,

Of course you are right if the data is stored that way.

However, I have to do these alot and the issue I face is I think similar to OnCrystal's.

The Ticket Opened Date is Stored and the Closed Date is stored in the same record.

To do the aging for actual working days and hours, you need to use a formula like Ken's (I have a similar one that I did) so that you can subtract the non-working days and hours from the age.

There is a big difference in aging for a call opened at 4:45 on a Friday (working hours are M-F 8 - 5) and

closed at 8:15 on a Monday, really only aged 1/2 hour. However, if you just subtract those or even use date diff, you will get somewhere around 49 hours!

Unfortunately, you can't always modify the database.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.