7 Replies Latest reply on Dec 15, 2008 2:37 PM by shogun

    "Weeks till vacation" calculation

    shogun

      Title

      "Weeks till vacation" calculation

      Post

      Hello-

      I'd like some guidance with the following calculation.  

      I'd like to be able to calculate the "weeks to go" for a particular due date.  Think of it as a count down to a particular date using the current date as the start date. I'd like the result shown as a decimal. Each day of the week would have a value of .2, only weekdays would be counted.  For example, a week and three days would be expressed as 1.6 weeks and four days until the due date would be .8 weeks.     

      Any help would be greatly appreciated.

      Thank you.

        • 1. Re: "Weeks till vacation" calculation
          TSGal

          shogun:

           

          Thank you for your post.

           

          There are two parts to this calculation.  Let's start with the easy part....

           

          To get the number of weeks, the formula would be:

           

          Int ( (Due Date - Get (CurrentDate) ) / 7 )

           

          That is, we take the Due Date and subtract the current date.  This give us the number of days.  Dividing by 7 will give us the number of weeks.  We use the Int() function to take the integer result.  For example, using 1/1/2009 as Due Date and 15/12/2009 as today's date, the difference is 17 days.  Dividing by 7 results in 2.428571.  Using the Int() function results in 2.

           

          Now comes the second (and tricky) part...

           

          Using the example above, there are two extra days.  In this case, they are two week days, but what would happen if the extra days occur over the weekend?  Let's look at the table below....

           

                    Mon  Tue  Wed  Thu  Fri

          Mon      0      1      2      3     4

          Tue       4     0      1      2     3

          Wed      3     4      0      1     2

          Thu       2     3      4      0     1

          Fri        1     2      3      4     0

           

           

          Each row is the day of the week for today's date.  The column is the day of the week for the Due date.  The cross reference is the number of business days leftover after figuring weeks.  For example, today's date (15/12/2008) is a Monday, so we look at the first row.  The Due date (1/1/2009) is a Thursday, and we see that the difference is 3 days.  If we look back at our earlier division, 17 days is 2 weeks and 3 days.  This is a simple solution.

           

          However, let's look at Due Date being 6/1/2009 and today's date being 1/1/2009.  The difference is 5 days.  However, 1/1/2009 is on a Thursday, and 6/1/2009 is a Tuesday.  If we look at the table row for Thursday, and it move over to the Tuesday column, we see the result of 3.  That is, Thursday to Friday, Friday to Monday, Monday to Tuesday.  Does this make sense?

           

          So, now how do we put this together?  We build a string, use a selector to grab the corresponding date and move over the number of days.  Let me give you the calculation first, and then give the explanation.

           

          Middle ("0123440123340122340112340"; (DayOfWeek (Get (CurrentDate)) - 2) * 5 + DayOfWeek (DueDate) - 1; 1)

           

          We put the table into a string.  One row after another.

           

          To extract which row, we'll take the Day of the week for today's date.  This returns 2 for Monday, 3 for Tuesday, etc.  Since we want to start at the first position for Monday, we'll subtract 2 from the result to get to the zero position.  We multiply that result by 5 to move us to the correct starting position of each group of five numbers.  We add to that the Day of the week for the Due Date, and since Monday through Friday gives us 2 through 6, we want to subtract one from that result.

           

          For our first example, today's date is Monday (2), subtract 2 (= zero) and multiply by 5 (zero) to get us to the beginning of the string.  Since the Due Date for 1/1/2009 is a Thursday, Day of week returns 5 subtract 1, and that leaves us the result of 4.  We then move to the 4th position in the string which is "3"; our desired number.

           

          Since this gives us a result from 0 to 4, we can divide the result by 5 to get .2, .4, .6, and .8.

           

          Putting this all together, the calculation would be:

           

          Int ( (Due Date - Get (CurrentDate) ) / 7 ) + 

          Middle ("0123440123340122340112340"; (DayOfWeek (Get (CurrentDate)) - 2) * 5 + DayOfWeek (DueDate) - 1; 1) / 5 

           

          If you should have any additional questions, or need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: "Weeks till vacation" calculation
            Orlando
              

            Hi shogun

             

            Welcome to the forum.

             

            I have this Custom Function that will work out the number of weekday between two dates, you can use this and then divide the result by 5.

             

            The Custom Function is as follows 

             

             xCF_WorkDayCount ( startDate ; endDate )

             

              Let ( 

              DayInitial = Left ( DayName ( startDate ) ; 1 ) 

              ;  // Start of calculation

             

              Case ( DayInitial = "S" ; xCF_WorkDayCount ( startDate + 1 ; endDate ) ;

              startDate > endDate ; "" ;

              1 + xCF_WorkDayCount ( startDate + 1 ; endDate )

             

              ) // End of Case function

             

              ) // End of Let function 

             

            If you have never used custom functions before, you can setup the custom function by going:

              

            • 'File > Manage > Custom Functions...'
            • Click 'New'
            • Then copy and paste the above calculation into the appropriate fields
            • Function Name: = xCF_WorkDayCount
            • Function Parameter: = startDate and endDate, click the green plus sign
            • Then the rest in the calculation box.
            • Click 'OK'

            And the function will display near the bottom of the Calculation Function list, or you can view "Custom Functions" 

             

            • Create a new calculation field in your table

             

              WeeksToGo ( Calculation ; number ) =

                xCF_WorkDayCount ( startDate ; endDate ) / 5

             

            This shoudl give you the result your after.

             

            Let me know how you get on. 

            • 3. Re: "Weeks till vacation" calculation
              shogun
                

              TS Gal-

              Excellent!

              Your explanation is clear and concise, exactly what I was looking for, thank you.  I do however have a couple of follow up questions...

               

              1. I'd like the formula to count all the weekdays [starting & ending] in the range including the current date and the due date.  If the current date is 12/15/08 and the due date is 12/12/08 I'd like a return of 1.2.  If I omit the -1 [* 5 + DayOfWeek (DueDate) - 1; 1) / 5 ] from the end formula I get the correct result, is the correct/best way to express?

               

              2. Is it possible to get the formula to work in reverse as well?  If the current date is 12/15/08 and the due date was 12/12/08 the result would be -.4 

               

              And finally.... 

               

              3. Is it possible to add some conditional formatting [I'm using 8.5] so as the due date approaches, say one week out from being due, the font/field color would change to orange and after the due date the font/field would be red?  Any thoughts?

               

              Thank you again. -Shogun 

              • 4. Re: "Weeks till vacation" calculation
                TSGal

                shogun:

                 

                Your description in #1 is probably 12/22/08; not 12/12/08.  Correct?

                 

                Yes, if you leave off the -1 on the end, you MIGHT not get the right answer.  Although the example I gave you would provide the correct result, if the start date occurred on a Monday, and the end date occurred on a Friday, you would still have the same result.  Instead, I would just add .2 to the final result.  That is:

                 

                Int ( (Due Date - Get (CurrentDate) ) / 7 ) + 

                Middle ("0123440123340122340112340"; (DayOfWeek (Get (CurrentDate)) - 2) * 5 + DayOfWeek (DueDate) - 1; 1) / 5 + 0.2

                 

                For #2, it works a bit differently. but the concept is similar.  We could use an entirely different table and numbers to subtract, but you can leverage what is already there.

                 

                Let's use your example.  The Due Date occurs before today's date.

                Due Date - Get (CurrentDate) = -3

                Dividing by 7 =  -0.428157 

                Int () function = 0

                 

                Middle function returns = .8 (without adding 0.2)

                 

                Since you want -0.4, we can do this by subtracting 1 (-0.2) and then subtract from the result.  Let's do the following:

                 

                If (Due Date ≥ Get (CurrentDate); 0; -1 ) +

                Int ( (Due Date - Get (CurrentDate) ) / 7 ) + 

                Middle ("0123440123340122340112340"; (DayOfWeek (Get (CurrentDate)) - 2) * 5 + DayOfWeek (DueDate) - 1; 1) / 5 +

                If (Due Date ≥ Get(CurrentDate); 0.2; -0.2 )

                 

                Try this out and let me know if this works for you.

                 

                Regarding point #3, conditional formatting was introduced in FileMaker Pro 9.  The only thing I can think of is to create a couple of calculation fields and place them one on top of the other.  Assuming the calculation field above is named "Countdown"....

                 

                Orange = If ( Countdown ≤ 1 and Countdown ≥ 0; "ONE WEEK"; "" )

                 

                Red = If (Countdown < 0; "OVERDUE"; "" )

                 

                Put these fields on the layout and format them to show the colors orange and red, respectively.

                 

                If Countdown is within a week of coming due, it will display "ONE WEEK" in orange.  Otherwise, it will not display.  Once Countdown becomes due, "OVERDUE" is displayed in red.  Otherwise, it will not display.

                 

                This is a simplistic case.  If the record is completed, then you don't want them to display, so you'll need to add another criteria to the calculations.  For example for Red

                 

                If (Countdown < 0 and Completed = "No"; "OVERDUE"; "" )

                 

                I hope this helps.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: "Weeks till vacation" calculation
                  shogun
                    

                  TSGal,

                   

                  Your formula for example 2 works great looking forward, a due date one week from today correctly returns 1.2 weeks, however for dates that have expired it seems to be adding an additional week.  For example one week in the past from today, returns -2.2 weeks.

                   

                  For the conditional format how would I apply this to the formula we've been discussing,  I would like the fill color of the due date field to change one week out to "orange" and "red" for overdue dates.    

                   

                  Thank you,

                  shogun 

                  • 6. Re: "Weeks till vacation" calculation
                    TSGal

                    shogun:

                     

                    Yes, the formula is incorrect for the negative values when the day of the week for Due Date and CurrentDate are the same.  Therefore, I modified the calculation "Countdown" as follows:

                     

                    If (Due Date ≥ Get (CurrentDate) or DayOfWeek (Due Date) = DayOfWeek (Get (CurrentDate) ); 0; -1) + 

                    Int ( (Due Date - Get (CurrentDate) ) / 7 ) +

                    Middle ("0123440123340122340112340"; (DayOfWeek (Get (CurrentDate)) - 2) * 5 + DayOfWeek (Due Date) - 1; 1) / 5 +

                    If (Due Date ≥ Get (CurrentDate); 0.2; -0.2 )

                     

                    Since you have FileMaker Pro 8.5, you do not have the ability to perform conditional formatting.  The only advice I have is put two additional fields on top of the Due Date field, and set them to be transparent.  The calculation for these fields would be similar to what I gave you previously.  That is:

                     

                    Orange = If (Countdown ≤ 1 and Countdown ≥ 0; Due Date; "" )

                     

                    Red = If (Countdown < 0; Due Date; "" )

                     

                    Put both of these fields on the layout.  Select the field "Orange", pull down the Format menu and select "Text Color..." and select orange color.  Select the field "Red", pull down the Format menu and select "Text Color..." and select red color.

                     

                    Select each field.  On the left side of the screen, near the bottom, below the paint bucket is a color wheel.  Click on the pattern to the right of that, and select the upper left icon (two clear squares).  This provides see through capability.

                     

                    Now, put both of these fields on top of the Date Due field.  When Countdown is less than 1 (and greater than 0), the Orange date will appear.  When less than 0, the Red date will appear.

                     

                    Make sure the field is perfectly on top of Date Due.  Otherwise, you'll see a shadow effect.

                     

                    Let me know how this works.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: "Weeks till vacation" calculation
                      shogun
                         TSGal - Thanks works great.:smileyhappy: