1 Reply Latest reply on Aug 12, 2016 8:47 AM by philipHPG

    Caclulating weekdays in a date range

    MikeWile

      Is there an accurate formula to calculate the weekdays in a range? I went to Calculating Number of Weekdays (Work Days) Between Dates | FileMaker but it doesn't calculate correctly. If I enter 8/1/2016 as start and 8/12/2016 I get 9; it should be 10. If I enter 8/1/2016 as start and 8/13/2016 I get 10, which is corrrect as 8/13/2016 is a Saturday.

        • 1. Re: Caclulating weekdays in a date range
          philipHPG

          The formula you referenced calculates the number of workdays *between* the two dates mentioned, it does not include the End Date. If you want the calculation to be inclusive (including the End Date), add one to the End Date before calculating, like this:

           

          Let ( [

               StartDate = StartDateField ;

               EndDate = EndDateField + 1

          ] ;

               5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )

          )