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.
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 )
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:
And the function will display near the bottom of the Calculation Function list, or you can view "Custom Functions"
- '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'
- 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.
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
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
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:
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.
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; "" )
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 - Thanks works great.:smileyhappy: