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

    waldotippy

      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

           [http://help.filemaker.com/app/answers/detail/a_id/5245/related/1]:

           

                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))