3 Replies Latest reply: May 15, 2014 9:57 AM by NGIESEKE _ RSS

    Net Working Days (excel) in Monarch V8.01

    TSBusAnalyst _

      I'm sure it been covered but can't find it.  Can anyone help me with calculating Net Working days in Monarch v8.01

       

      ex  6/1/05 to 9/30/05 - How many working days?

        • Net Working Days (excel) in Monarch V8.01
          Andy Abbott

          There may be a simpler solution than this, but here's what I've come up with.  I've tested it and it seems to do what you want.

           

          1) First, find the number of weekdays to include from the week in which the starting date falls:

           

          Create a calculated field called "Begin Day" using the function Weekday(beginning date).  Use this field as the input field for the following lookup table:

           

          "Begin Day"       "First Week Days"

          (input)           (output)

          1                 5

          2                 5

          3                 4

          4                 3

          5                 2

          6                 1

          7                 0

           

          If the beginning date is a Tusday, for example, this will give you 4 (Tue through Fri) as the number of days to include from the first week.

           

          2) Second, find the number of weekdays to include from the week in which the ending date falls:

           

          Create a calculated field called "End Day" using the function Weekday(Ending date).  Use this field as the input field for the following lookup table:

           

          "End Day"     "Last Week Days"

          (input)       (output)

          1             0

          2             1

          3             2

          4             3

          5             4

          6             5

          7             5

           

          If the last date is a Saturday, for example, this will give you 5 (Mon through Fri) as the number of days to include from the last week.

           

          3) Find the days to add from the full weeks between the beginning & ending dates

           

             a) Using another lookup field, find the date of the Sunday following the beginning date:

           

          "Begin Day"     "Days to Add"

          (input)         (output)

          1               7

          2               6

          3               5

          4               4

          5               3

          6               2

          7               1

           

          The date of the first Sunday is then: Beginning Date + Days to Add.

           

             b) Use the weekday of the ending date to find the date of the Saturday preceding the ending date:  Last Saturday = Ending Date - Weekday(Ending Date)

           

             c) The formula to find the number of weekdays of the full weeks (Sun through Sat) between the beginning and ending dates is (we'll call this field Full Week Days): (Last Saturday - First Sunday + 1) / 7 * 5

           

          4) Add all the days together, taking into account that there may not be a full week between the beginning and ending dates (the calculated field Last Saturday would then be a day before the calculated field First Sunday).

           

          The total days is then:

           

          If(First Sunday - Last Saturday = 1, First Week Days + Last Week Days, First Week Days + Full Week Days + Last Week Days)

           

          Using your example above, 6/1/05 to 9/30/05, I get an answer of 88 days.

           

          This does not take into account that there may be fewer than 7 days between the beginning & ending dates; it is assumed it would just be easier to just count the days in this case.

           

          Calculations can get involved, but the nice thing about Monarch is that you do it once, then it is saved in your model.  Best of luck.

          • Net Working Days (excel) in Monarch V8.01
            Andy Abbott

            One clarification to the above solution: the next to last paragraph should read, "This does not take into account that there may be fewer than 7 days between the beginning & ending dates 'within the same week.'"

             

            Here's another solution that does take this into account, but it does have one lengthy lookup table (49 lines).

             

            This approach is to find the number of weekdays in the number of full weeks and in the partial week that is left over, then add them together.

             

            Given a starting date and an ending date (Start & End), create a calculated field, Total Days:

             

            (End - Start + 1)

             

            Create a calculated field, Weeks:

             

            / 7 (I'm giving this field 2 decimal places)

             

            Create a calculated field, Full Weeks.  This is the integer part of Weeks:

             

            Int(Weeks)

             

            Create a calculated field Full Week Weekdays:

             

            * 5

             

            Create a calculated field, Partial Week Days.  This is the number of days left over, after the number of full weeks:

             

            Mod(,7)

             

            Create a calculated field, Start Day, the day of the week of the starting date:

             

            Weekday(Start)

             

            The number of weekdays within Partial Week Days depends on (1) the number of days in Partial Week Days and (2) the day of the week of the starting date.  If the starting date is on a Tuesday, for example, the first full week will be Tuesday through the following Monday, and the first day of the "mod" will be a Tuesday.  We can then create a lookup table that takes into account any combination of the Partial Week Days and the Start Day.

             

            This can be a calculated lookup field, or if you have Monarch Pro, an Excel file used as an external lookup (since I've done this, I can e-mail it to you if you'd like).  We'll create a calculated text field, Days & Start Day, with this formula:

             

            Str(,1) + Str(,1)

             

            This will be used as the input field for a lookup table to find Mod Weekdays:

             

            "Days & Start Day"  "Mod Weekdays"

            (input)  (output)

            01 0

            02 0

            03 0

            04 0

            05 0

            06 0

            07 0

            11 0

            12 1

            13 1

            14 1

            15 1

            16 1

            17 0

            21 1

            22 2

            23 2

            24 2

            25 2

            26 1

            27 0

            31 2

            32 3

            33 3

            34 3

            35 2

            36 1

            37 1

            41 3

            42 4

            43 4

            44 3

            45 2

            46 2

            47 2

            51 4

            52 5

            53 4

            54 3

            55 3

            56 3

            57 3

            61 5

            62 5

            63 4

            64 4

            65 4

            66 4

            67 4

             

            The final calculated field, Total Weekdays, is then:

             

            +

            • Net Working Days (excel) in Monarch V8.01
              NGIESEKE _

              I think I may have a solution that does not involve a VLOOKUP.  Hope this works.

               

              (((ENDDATE-(WEEKDAY(ENDDATE,2))1)-(BEGINDATE(8-(WEEKDAY( BEGINDATE ,2)))))/7)*5(WEEKDAY(ENDDATE,2))IF((WEEKDAY( BEGINDATE ,2))>5,0,(5-(WEEKDAY( BEGINDATE ,2))))

               

              By making the formula WEEKDAY(ENDDATE,2) the 2 will make Monday be 1 and Sunday 7 instead of Sunday being 1.  It make the same formula later find out if the day it is calculating is the weekend >5.