3 Replies Latest reply on Jan 2, 2010 8:15 AM by LaRetta_1

    How many Mondays

    mckostan

      Title

      How many Mondays

      Post

      Hi,

       

      I need to find a way to calculate the number of monday between two dates.

       

      Thanks

      MC 

        • 1. Re: How many Mondays
          LaRetta_1
            

          It would depend upon whether you wanted inclusive, for instance, Date1 is 12/7/2009 and Date2 is 12/21/2009 and you start counting forward on 12/8, you would have 2 Mondays.  But if you count the 7th (inclusive), it would be 3 Mondays.

           

          Exclusive:  Div ( date2 - ( Date1 - Mod ( Date1 - 1 ; 7  ) ) ; 7 )

          Inclusive: Div ( date2 - ( Date1 - Mod ( Date1 - 1 ; 7  ) ) ; 7 ) + ( DayOfWeek ( Date1 ) = 2 )

           

          I believe there are prettier calcs around but this is what came to me this early morning. :smileyhappy:

           

          UPDATE:  Yeah, no doubt Comment's calc would be more elegant.

          • 2. Re: How many Mondays
            mckostan
              

            Hi LaRetta,

             

            The "inclusive" worked. If you don´t mind can you explain the logic on the Mod part? "Mod ( Date1 - 1 ; 7 )"

             

            Many Thanks

             

            • 3. Re: How many Mondays
              LaRetta_1
                 <!--   [if gte mso 9]&amp;gt;&amp;lt;xml&amp;gt; &amp;lt;w:WordDocument&amp;gt;   &amp;lt;w:View&amp;gt;Normal&amp;lt;/w:View&amp;gt;   &amp;lt;w:Zoom&amp;gt;0&amp;lt;/w:Zoom&amp;gt;   &amp;lt;w:PunctuationKerning/&amp;gt;   &amp;lt;w:ValidateAgainstSchemas/&amp;gt;   &amp;lt;w:SaveIfXMLInvalid&amp;gt;false&amp;lt;/w:SaveIfXMLInvalid&amp;gt;   &amp;lt;w:IgnoreMixedContent&amp;gt;false&amp;lt;/w:IgnoreMixedContent&amp;gt;   &amp;lt;w:AlwaysShowPlaceholderText&amp;gt;false&amp;lt;/w:AlwaysShowPlaceholderText&amp;gt;   &amp;lt;w:Compatibility&amp;gt;    &amp;lt;w:BreakWrappedTables/&amp;gt;    &amp;lt;w:SnapToGridInCell/&amp;gt;    &amp;lt;w:WrapTextWithPunct/&amp;gt;    &amp;lt;w:UseAsianBreakRules/&amp;gt;    &amp;lt;w:DontGrowAutofit/&amp;gt;   &amp;lt;/w:Compatibility&amp;gt;   &amp;lt;w:BrowserLevel&amp;gt;MicrosoftInternetExplorer4&amp;lt;/w:BrowserLevel&amp;gt; &amp;lt;/w:WordDocument&amp;gt; &amp;lt;/xml&amp;gt;&amp;lt;![endif]   --><!--   [if gte mso 9]&amp;gt;&amp;lt;xml&amp;gt; &amp;lt;w:LatentStyles DefLockedState=&amp;quot;false&amp;quot; LatentStyleCount=&amp;quot;156&amp;quot;&amp;gt; &amp;lt;/w:LatentStyles&amp;gt; &amp;lt;/xml&amp;gt;&amp;lt;![endif]   --><!--   /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&amp;quot;&amp;quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&amp;quot;Times New Roman&amp;quot;;      mso-fareast-font-family:&amp;quot;Times New Roman&amp;quot;;} code      {font-family:&amp;quot;Courier New&amp;quot;;      mso-ascii-font-family:&amp;quot;Courier New&amp;quot;;      mso-fareast-font-family:&amp;quot;Times New Roman&amp;quot;;      mso-hansi-font-family:&amp;quot;Courier New&amp;quot;;      mso-bidi-font-family:&amp;quot;Courier New&amp;quot;;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}    --><!--   [if gte mso 10]&amp;gt; &amp;lt;style&amp;gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&amp;quot;Table Normal&amp;quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&amp;quot;&amp;quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&amp;quot;Times New Roman&amp;quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &amp;lt;/style&amp;gt; &amp;lt;![endif]   -->

              Mod() per FM Help … Returns the remainder after number is divided by divisor. 

               

              Dates are numbers of days since 1/1/0001 (which was a Monday ).  It is more difficult to move forward/backward using DayOfWeek() because, if you go backwards through the days, it doesn't switch from 2 to 1 to 7.  Mod() allows jump (and span) through week boundaries.

               

              Mod() tells us the left-over days after the division by 7 on any date eliminates all full weeks.  I wanted to jump back to the prior Monday always to begin counting forward.  I couldn't use Mod ( start ; 7  ) + 1 instead of Mod ( start – 1 ; 7 ) because that would have jumped me forward if the start day was Sunday as would DayOfWeek ( start ) + 2 or even DayOfWeek ( start - 1 ) + 8.  I could have also used DayOfWeek ( start - 1 ) + 1 but just tend to focus on using Mod() more than DayOfWeek().

               

              So, after dropping back to prior Monday, I subtract the difference from the end date and divide by 7 to give total number of full weeks (Mondays).  I am not a DateMaster; I just enjoy working with dates.