working days only...

[lakeland]

Can anyone help me with a problem i've recently come across?...

I need to perform a check which display a piece of information when when it is greater than 5 days old. The only problem is to include WORKING DAYS only (mon-fri).

This firstly came across very simple (localdate-7) which would take into account friday and saturday), but if the information was inputted on a weekend the formula fails...

has anybody come across anything like this before?

If so PLEASE reply to make with any suggestions.

THANK YOU!

lakeland

 

[mbarron]

The following will work if you have CR version 8 or higher:

local numbervar counter;datevar lastday;datevar firstday;numbervar loops;
firstday:={first.day.field};lastday:={last.day.field];

for loops:=0 to lastday-firstday do(
if dayofweek(firstday+loops)in [2 to 6] then
counter:=counter+1 else counter:=counter;
if counter>5 then loops:=lastday-firstday);

If counter>5 then "Flag"

If you find the postings here in the Tek-Tips Forum helpful, please let us know by using the "Click here" option. (you don't have to be the poster) It's painless and lets us know our help has been useful and is appreciated.

Mike

 

[lakeland]

Unfortunately i am using version 7.0 at the moment. My company is in the process of upgrading to 8.5, so until then i have to make use with the earlier version.

Do you have the formula which will work for version 7.0?

many thanks

lakeland

 

[mbarron]

The following is from Ken Hamady's site.

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

WhilePrintingRecords;
DateVar Start := {table.StartDate}; //Replace this field with your Starting Date field
DateVar End := {table.EndDate}; //Replace this field with your Ending Date field
NumberVar Weeks;
NumberVar Days;
NumberVar Hol:= 0;

//Figure the number of Calendar "Rows" involved and count 5 days for each:
Weeks:= (Truncate (End - dayofWeek(End) + 1 - (Start - dayofWeek(Start) + 1)) /7 ) * 5;

//Adjust the first and last weeks based on when in the week you start and end
Days := DayOfWeek(End) - DayOfWeek(Start) + 1
+ (if DayOfWeek(Start) = 1 then -1 else 0) //adjust for starting on a Sunday:
+ (if DayOfWeek(End) = 7 then -1 else 0); //adjust for ending on a Saturday:

//Adjust for Holidays in the period between the start and end dates:
if Date(1999,01,01) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,01,18) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,02,16) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,05,31) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,09,06) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,11,25) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(1999,12,24) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,03) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,01,17) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,02,14) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,05,29) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,09,04) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,11,23) in start to end then Hol:= Hol+1 else Hol:= Hol;
if Date(2000,12,25) in start to end then Hol:= Hol+1 else Hol:= Hol;

//Assemble the adjusted work days
Weeks + Days - Hol If you find the postings here in the Tek-Tips Forum helpful, please let us know by using the "Click here" option. (you don't have to be the poster) It's painless and lets us know our help has been useful and is appreciated.

Mike

 

[LyleW]

Try the CrystalDecisions.com site. go to support and downloads, then UFL's there are a batch of functions relating to Business days