1 2 Previous Next 18 Replies Latest reply on Mar 26, 2009 10:22 AM by Guildy

    How many weeks between dates calculation

    shogun

      Title

      How many weeks between dates calculation

      Post

      Hello-

      I'd like some help with the following calculation.  

      I'd like a formula to calculate the time differential between to dates, M-F is the working range however I'd like the result as a decimal.  

      For example: if the start date is a Monday and the finish date is the following Wednesday the result would be 1.6 Weeks.   If the start date is a Monday and the finish date is the Wednesday of the same week the result would be .6 Weeks, as weekdays in the ranges should be counted as work days.

      Here is the formula I'm working with so far, the result works for total days however I'm having trouble reducing to weeks.  

      Thank you, -S

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

       

       

        • 1. Re: How many weeks between dates calculation
          philmodjunk
             Perhaps you could include the function WeekOfYear() in your expression? You'd have to be careful when your dates fall in different years though.
          • 2. Re: How many weeks between dates calculation
            rdulys
              

            The followong calc has worked for me...

             

            Case(IsEmpty(End Date); "";IsEmpty(Start Date); "";Int((End Date - Start Date) / 7) * 5 + Mod(End Date - Start Date; 7) -If(Mod(End Date - Date(1;3;1904);7) < Mod(Start Date - Date(1;3;1904); 7); 2; 0))+1

             

            Then take the resulting field and devide it by 7, then use number format on that field and click on "Format as Decimal" and then set fixed number of decimal digits to 1.

             

            I also have another field where I put a number representing the number of Holidays between those 2 dates and it subtracts that from the total so holidays are not counted in those weeks, but you may not ned that!

             

            Hope this works for you!

             

            • 3. Re: How many weeks between dates calculation
              TSGal

              shogun:

               

              Thank you for your post.

               

              The calculation you have does give you the number of work days.  Divide that result by 5 and you have your weeks and decimals.  Using your example, if the start date is a Monday and the finish date is the following Wednesday, you get the result of 8 days.  If you divide by 5, you get 1.6.  If the start date is a Monday and the finish date is the Wednesday of the same week, you get 3 days.  Dividing by 5 results in .6.  Does that make sense?

               

              TSGal

              FileMaker, Inc. 

               

               

              • 4. Re: How many weeks between dates calculation
                rdulys
                  

                I tried his example calc in the original post and when I put a start date of 3/2/09 and end date of 3/11/09 which is Monday and Wednesday I get 7 days or 1.4 weeks.  It is off by 1 day as I can see.

                 

                Does the same think with other dates also!

                 

                Anyone else?

                 

                • 5. Re: How many weeks between dates calculation
                  comment_1
                     Do you understand what your calculation does?
                  • 6. Re: How many weeks between dates calculation
                    rdulys
                      

                    comment wrote:
                    Do you understand what your calculation does?

                    Who are you asking???  If you are asking me, then, NO, not completely.  I had help a few years ago with this one and I am just passing it on to help others.  I have used it for about 4 years now on a daily basis and the number it outputs has been correct...  And I use it in a span of up to 50 weeks, sometimes into new years and it hasn't failed me yet...

                     

                    I have 'tried' to break it down in parts to see how it was working but only parts makes sense to me.  So, since it was working for me I haven't look at the calc in quite a while.

                     

                    If you were NOT asking me the question, then disregard everything I said above :)

                     


                    • 7. Re: How many weeks between dates calculation
                      TSGal

                      rdulys:

                       

                      The calculation definitely finds the days between the two days, but does not include the day.  For example, Monday to Wednesday is two days (which is what the calculation returns), but since you want three days, add one.  Therefore, your calculation can be changed to:

                       

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

                       

                      Try this out and see if this works.

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: How many weeks between dates calculation
                        rdulys
                          

                        TSGal wrote:

                         

                        The calculation definitely finds the days between the two days, but does not include the day. 


                        I got you, I guess that makes sense.  Either way I would think one of these post should satisfy the original poster...

                         

                        • 9. Re: How many weeks between dates calculation
                          comment_1
                            

                          rdulys wrote:

                          Who are you asking??? 


                          Does it matter? It was a rhetorical question.


                          • 10. Re: How many weeks between dates calculation
                            rdulys
                              

                            comment wrote:

                            Who are you asking??? 


                            Does it matter? It was a rhetorical question.


                             

                            Then why post it in a forum where people post questions in hopes of getting an answer?

                             

                             


                            • 11. Re: How many weeks between dates calculation
                              comment_1
                                

                              To express an opinion.

                              • 12. Re: How many weeks between dates calculation
                                rdulys
                                  

                                comment wrote:

                                To express an opinion.


                                Oh, ok, I just haven't read the post with your opinion on it yet.  Just the post where you ask a question, but don't really expect an answer.

                                 

                                Thanks!


                                • 13. Re: How many weeks between dates calculation
                                  shogun
                                    

                                  Thanks to TSGal and rdulys.  Problem solved!

                                   

                                   

                                  • 14. Re: How many weeks between dates calculation
                                    Guildy
                                      

                                    Hello

                                     

                                    I wonder if it is possible to modify the calculations shown in this thread to calculate the actual number of weeks (as apposed to working days within a week)?

                                     

                                    I am trying to calculate rents due on property. With help from this forum I have got number of months between two dates sorted but am stuck on weeks.

                                     

                                    E.G.:

                                     

                                    Start date = Wednesday 18 March 2009 - Today's date = Wednesday 25 March 2009. This should be 1 week (actually for my purpose it will be 2 weeks because 2 rents will have become due but that can be dealt with by adding 1)

                                     

                                    Then a simple formula of number of weeks * rent gives me total rent due. 

                                     

                                    I will shortly need a 4 four weekly calculation and 2 calendar monthly between two dates calculation but let's get the basics first!

                                     

                                    Many thanks to anyone who can assist.

                                     

                                    Adrian

                                    1 2 Previous Next