Work Days calculation with a twist!

[normdouglas]

Please please please help.

I have been developing in FMP for a long time, and always get stuck on this type of stuff.

Here is the problem.

Need to work out what number WORK DAY it currently is. Using Start Date (1st of the month) and End Date (today) and allow for varying number of work days (ie open Mon-Fri or Mon-Sat or 7 days). Here's what I have and it DOES ACTUALLY WORK. However not for every date. If I put in 1/8/2004 thru to 6/8/2004 with working days I get 5 days and I am happy, if I put in the 8/8/2004 I get 5, still happy, if I put in 7/8/2004 I get 6 now I'm pissed.

The Saturdays seems to stuff it up. I got the calc from one of the 5.5 templates and have looked at many others but they don't really fit my need.

Can someone tell me what's wrong with my calc

FIELDS: StartDate (date)

EndDate (date)

DaysOpen (number)

CurrentTradingDay(number calculation)

CurrentTradingDay =

Int((EndDate - StartDate)/7) * DaysOpen + Mod(EndDate - StartDate,7) - If(DayofWeek(EndDate) < DayofWeek(StartDate),1,0)

Please Help.

Norm

p.s I know I am new but I promise I will contribute to this forum from now on.

[RalphL]

FileMaker Tech Article 108080 gives the following for a 5 day week:

"Int((EndDate - StartDate)/7) * 5 + If(DayofWeek(EndDate) < DayofWeek(StartDate),

Min(5, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)),

If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) -

DayofWeek(StartDate), 0))

If you want to be sure and count the beginning day, just add 1 to the calculation:

Int((EndDate - StartDate)/7) * 5 + If(DayofWeek(EndDate) < DayofWeek(StartDate),

Min(5, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)),

If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) -

DayofWeek(StartDate), 0)) + 1"

I think you would replace the constants with a calculation based on the Days Open.