counting work days only

[Bob T]

senerio, trying to count the number of days it takes to move product from one dept. to the next but not including Sat and Sun (here is the kicker)and every other Friday. I have date stamps on the transactions to and from and a calculation that gives me the cycle time, but that includes our days off. How do I get a realistic cycle time?

[Matthew F]

It sounds to me like you do not have a well defined mathematical way of determining whether any given day is a work day or not. Whether any given Friday is a workday is idiosyncratic because it depends on your particular schedule. What about holidays, etc.

In this context you might want to create a Calendar table with one record for each day of the year. in which you can designate a 'Work Day' field with 1's, vs 0's for off days. To calculate the number of work days in a range just do a Find for the dates meeting your criteria and then sum up the Work Day field.

[comment]

This is indeed a "kicker". Try =

Let ( [ 

pattern = "10000011000011" ;

n = EndDate - StartDate + 1 ;

w = Div ( n ; 14 ) ;

r = Mod ( n ; 14 ) ; 

s = Mod ( StartDate ; 14 ) + 1 ; 

p = Middle ( pattern&pattern ; s ; r ) ;

x = w * PatternCount ( pattern ; "1" ) + PatternCount ( p ; "1" )

] ;

n - x

)

I haven't tested this very thoroughly. Note that this assumes Friday Nov 4, 2010 is a workday.

[comment]

you might want to create a Calendar table with one record for each day of the year. in which you can designate a 'Work Day' field with 1's, vs 0's for off days.

I believe a table of holidays only would be quite sufficient. To account for holidays in the given range, you can use a relationship and subtract the related records count.

Note that holidays that fall on weekends must be excluded from the count.

[bcooney]

If you have Advanced, you could use Daniele's CF:

http://www.briandunning.com/cf/1182

[comment]

Could you?

[Bob T]

I do have advanced and I will try this. I looked at it briefly and looks to be exactly what I need. Further more, I can make every other Friday a holiday. SWEET!

Thanks all for the responses and I will post my results.

[comment]

I can make every other Friday a holiday.

LOL, why don't you make every Sunday and Saturday a holiday, too - then you won't need any complex calculations or custom functions at all...

[Raybaudi]

Could you?

Couldn't you ?

Was once a good habit to say each other any problem.

[comment]

Another good habit is reading the original question.... :

not including Sat and Sun ... and every other Friday.

[Raybaudi]

I read the original question but I thought you had found an error in that CF ( which is not too strange : )

[bcooney]

No, Daniele, comment was asking me if I had read the original question. I had. I think knowing these CFs exist is helpful in itself. Who knows whether every other Friday can be taken literally--he might just need to enter all exempt days as Holidays.

[Bob T]

ALL IS WELL

Another wrinkle on the brain. This has been a tremendous help for me.

I believe I would have eventually found a solution, yet God knows how long it would have taken.

I appreciate all your help

Regards,

Bob