7 Replies Latest reply on Jul 31, 2014 6:14 PM by JohnWolcott

    Measure number of weeks between dates

    matronryan

      Title

      Measure number of weeks between dates

      Post

      I'm new (sorry!) and have searched the forums for this but do not understand the solutions.  I need to measure the number of weeks between two dates - how do I do that?

      Thank you in advance

        • 1. Re: Measure number of weeks between dates
          philmodjunk

          Can you give some examples and the results you want to see?

          Example:

          If have these two dates: Monday, October 11, 2010 and Sunday, October 17, 2010. What "result" do you need to see? 1 week?

          If you have a week plus several days, do you want to discard the extra days or do some kind of rounding up for 4 or more days?

          • 2. Re: Measure number of weeks between dates
            matronryan

            Thank you PhilModJunk what I'm looking to do is work out the number of working weeks (i.e. Monday to Friday inc) between two dates. E.G number between 1/11/2010 and 30/11/2010 - perfect result would be 4 weeks 1 day.  I would round up 5 days or above (e.g. 6 or 7) to one week.

            Hope that makes sense

            Thanks

            Alan

            • 3. Re: Measure number of weeks between dates
              philmodjunk

              Will date one always fall on the same day of the week or could it fall in the middle of the work week? If it can fall in the middle of the week how does that affect the results you want to see?

              • 4. Re: Measure number of weeks between dates
                matronryan

                Both start date and end date could fall on any day. So if it starts on a Wednesday then I would need to could from the wednesday i.e. wednesday to tuesday would be 5 working days.

                But if it is a lot easier then I can make it a requirement to always start on a Monday

                Thanks

                • 5. Re: Measure number of weeks between dates
                  philmodjunk

                  The basic formula will be Div ( Date2 - Date1 ; 7 ) Where Date2 and Date1 are your two date fields.

                  This will give you the total number of complete weeks. We'll then need to look at Mod ( date2 - date1 ; 7 ) and what day of the week Date2 falls on,to examine the remaining days to see if this count should be increased by 1. The fact that any Saturdays and Sundays in these remaining days don't count will complicate this a bit.

                  Let ( [ shift = 2 - DayOfWeek ( Date1 ) ;
                             d1 = Date1 + shift ;
                            d2a = Date2  - ( DayOfWeek ( Date2 ) = 7 ) - 2 * ( DayOfWeek ( Date2 ) = 1 ) ;
                            d2 = d2a + shift
                          ] ;
                          Div ( d2 - d1 ; 7 ) + (Mod ( d2 - d1 ; 7 ) ≥ 2 )
                          ) // let

                  See if that works. It seems to produce the right results for the dates I've tested it with, but I may have missed a detail or have misinterpreted what you have asked for.

                  • 6. Re: Measure number of weeks between dates
                    KimmSun

                    THank you Phil, I was looking for the same formula except I would like to get the weeks and days.  For example, I would lke it to say 11w3d.  Can you help me with that?

                     

                    Kimm

                    • 7. Re: Measure number of weeks between dates
                      JohnWolcott

                           I cannot seem to get the calculation to work.  I have attached a copy of the calculation using the field names in my database (filemaker v13 on Win7).  First, I receive an error message when I try to close the calculation window unless I remove the last line, which is ") // let".  Second, if I remove the last line, the result I get is incorrect.  For example, I am using a start date of 5/19/2014 and an end date of 8/08/2014.  The result I get is "1".  If I use the simple Div(date2-date);7) I get "11" instead of 12".  If I use (date2-date1)/7 I get "11.6".  I'd like to get "12."

                           If I use Round((date2-date1)/7;0), I get what I am looking for, which is the number of 5 day work weeks, rounded to the nearest week, if the week starts on Monday and ends on Friday, but if starts on Tuesday and ends on a Friday, I'm a week short because if I subtract Tuesday from Friday (date2-date1) I get 3 days instead of 4 days.  I get pretty close if I add 1 to the number of days between the two dates, and at the moment, that is what I am doing. That is Round((date2 - date1 + 1)/7).  It means if someone started on Wednesday and ended on Friday, they wouldn't get credit for that week since if I round 3/7 I get 0.  Maybe I can live with that.  I could use Ceiling( ) instead of round, but then I give people credit for a full week if they just work one extra day.

                           Unfortunately I don't understand the solution by PhilModJunk at all so I cannot troubleshoot it on my own.  For example, "DayOfWeek (date2) = 7" would seem to me to return the number 7 regardless of the value of date2 and 2*DayOfWeek(date2) = 1 would be 2 so the definition of the variable d2a would be date2-5.  That is date2- (7-2*1).  I also don't understand the line "Div ( d2 - d1 ; 7 ) + ( Mod ( d2 - d1 ; 7 ) >_ 2)."