5 Replies Latest reply on Oct 3, 2012 12:38 PM by waldotippy

# networkdays calculation from filemaker.com failed today for the date 10/2/12

### Title

networkdays calculation from filemaker.com failed today for the date 10/2/12

### Post

it seems the equivalent of excel's network days formula supplied by filemaker [http://help.filemaker.com/app/answers/detail/a_id/5281/~/calculating-number-of-weekdays-%28work-days%29-between-dates] failed for me today.

for today the calculation = 4

it should be = -1

other days seem unaffected. just when calculating the current date and yesterday.

i had modified it slightly in my calculation field:

If ( IsEmpty( ENDDATE ) ; "" ;

(5 * Int ( ( ENDDATE - (Get(CurrentDate)) ) / 7 )

+

Middle ( "0012345501234544012343340123223401111234010012340" ;

7 * (DayOfWeek (  (Get(CurrentDate)) ) - 1 ) + DayOfWeek ( ENDDATE ) ;

1 )

)

)

)

i ended up replacing the calculation with this

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. Re: networkdays calculation from filemaker.com failed today for the date 10/2/12

Was 10/2/2012 the start or end date? And what date did you use for the other date needed for this calculation?

• ###### 2. Re: networkdays calculation from filemaker.com failed today for the date 10/2/12

oh!

(sorry)

enddate= 10/2/2012

startdate = current date = 10/3/2012

• ###### 3. Re: networkdays calculation from filemaker.com failed today for the date 10/2/12

this also worked for me. (current date = start date)

( Int ( ( endDate - 6 ) / 7 ) - Int ( ( Get(CurrentDate) - 6 ) / 7 ) ) * 5 +                  // the number of weeks times 5

If ( Mod ( endDate - 6 ; 7 ) - 2 > 0 ; Mod ( endDate - 6 ; 7 ) - 2 ; 0 ) -    // the number of weekdays of the last week

If ( Mod ( Get(CurrentDate) - 6 ; 7 ) - 2 > 0 ; Mod ( Get(CurrentDate) - 6 ; 7 ) - 2 ; 0 )    // the number of weekdays of the first week

• ###### 4. Re: networkdays calculation from filemaker.com failed today for the date 10/2/12

And why would the end date come before the start date?

That would seem to be a nonsensical pair of dates to use for this calculation in the first place.

You are correct that this calcualtion is not equipped to produce a negative value as it is basically a table lookup performed from with in a calculation.

• ###### 5. Re: networkdays calculation from filemaker.com failed today for the date 10/2/12

the end date would come before the start date if it was overdue.

so my mistake, i didn't really understand the formula i was using.

i had used it before but i'm guessing i never got into the negative values!
oops.

looking through i see my negatives values were off.

the upside is networkdays will now give a nice link to the filemaker solution.

if anyone is interested in learning how the formula works in more detail this helps:
http://fmforums.com/forum/topic/83842-working-out-the-number-of-working-days-between-2-dates-when-your-weekend-is-not-sat-sun/