6 Replies Latest reply on Jan 7, 2014 2:11 PM by genemapper

    Weekdays between two dates

    genemapper

      Hi everyone

       

      Am new to forum but not exactly new to FM

       

      Am using FM 12 advanced at the moment and need advice.

       

      I would like to find the number of weekdays (monday to friday) between two dates. my attempts so far have been complicated and have all failed.

       

      Any ideas for a simple solution?? thanks

        • 1. Re: Weekdays between two dates
          user14047

          /*==============================================================
              CustomFunction    BusinessDays ( startDate ; endDate )
          =============================================================*/
          Let (
              [
              THEDATE = startDate ;
              BD =
                not (
                  DayOfWeek ( THEDATE ) = 1 or     // Sunday
                  DayOfWeek ( THEDATE ) = 7     // Saturday
                )
              ] ;
               Case (
                not ( IsEmpty ( startDate ) or IsEmpty ( endDate ) ) and
                0 ≤ endDate - startDate ;

                  BD
                  + BusinessDays ( startDate + 1 ; endDate )
              )
          )

          • 2. Re: Weekdays between two dates
            erolst

            With additional error trapping:

             

            /* ==============================================================

                CustomFunction   WeekDays ( sD ; eD )

            ============================================================= */

            Let ( [

                dw = DayOfWeek ( sD ) ;

                 wd = not ( dw = 1 or dw = 7 )

                ] ;

                 Case (

                     eD < sD ; "Not valid" ;

                     IsEmpty ( sD ) or not Count ( sD ; eD ) ; "" ;

                     IsEmpty ( eD ) or sD = eD ; wd ;

                     wd + WeekDays ( sD + 1 ; eD )

                )

            )

             

            If you want the actual business days, you need another CF and/or a table with your applicable bank holiday dates, and add a check to the wd variable calculation (like “… or dw = 7 or isHoliday ( sD ) )” ).

            • 3. Re: Weekdays between two dates
              erolst

              pixi wrote:

               

              this should answer all your questions

               

              This CF seems to sport everything but the kitchen sink. What it doesn't do is answer the OP's original question …

              • 4. Re: Weekdays between two dates
                DavidJondreau

                Let( [

                total.days = end.date - start.date + 1 ;

                 

                start.day = DayOfWeek ( start.date ) ;

                end.day = DayOfWeek ( end.date ) ;

                start.balance = Case ( start.day = 7 ; -2 ; start.day = 1 ; -1 ; 0 ) ;

                end.balance = Case ( end.day = 7 ; -1 ; end.day = 1 ; -2 ; 0 ) ;

                 

                total.days.adjusted = total.days + start.balance + end.balance ;

                 

                total.weeks =  Div ( total.days.adjusted ; 7  ) ;

                total.week.ends = total.weeks  * 2 ;

                 

                result =  total.days.adjusted - total.week.ends

                 

                ];

                 

                result

                 

                )

                • 5. Re: Weekdays between two dates
                  genemapper

                  Dear David

                  thanks but this didn't work. I tried a similar approach. See Post from user 14047

                  • 6. Re: Weekdays between two dates
                    genemapper

                    Hi User14047

                     

                    Many thanks. Super CF. Took 2 mins to set up and runs perfectly....