
1. Re: Calendar Calculation to stay in work week
erolst May 4, 2014 1:12 PM (in response to robby@brays.net)Try this. Not too extensively tested. There is possibly a more elegant way than this brute force approach, but it should work.
Let ( [
dev = Date ( 5 ; 1 ; 2014 ) ; // SAMPLE DATA – your field
due = Let ( [ d = dev  2 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) ) ;
p1 = Let ( [ d = due  1 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) ) ;
p2 = Let ( [ d = p1  1 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) ) ;
p3 = Let ( [ d = p2  1 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) )
] ;
List ( due ; p1 ; p2 ; p3 )
)
returns
4/29/2014
4/28/2014
4/25/2014
4/24/2014

2. Re: Calendar Calculation to stay in work week
mikebeargie May 5, 2014 1:12 PM (in response to erolst)I always keep this bookmarked:
http://www.briandunning.com/filemakercustomfunctions/
So I can find things like this when I need it:

3. Re: Calendar Calculation to stay in work week
erolst May 5, 2014 4:39 PM (in response to mikebeargie)Mike Beargie wrote:
I always keep this bookmarked:
Right.
So I can find things like this when I need it:
But that's not exactly what the OP asked for – also, for 4 dates with partially different offsets, writing a recursiv function would be more trouble than it's worth …
OTOH, we could generalize this into something like CalculateMilestonesByOffset ( endDate ; offsetString ; milestoneNames ) …

4. Re: Calendar Calculation to stay in work week
mikebeargie May 5, 2014 4:50 PM (in response to erolst)Right, but I could modify that CF into something I could use to accomplish what Robby wanted. I'm not saying your calculation wasn't right (quite nice of you to write something like that for Robby), just saying that I normally use the custom functions bank as a jumping off point.

5. Re: Calendar Calculation to stay in work week
robby@brays.net May 5, 2014 5:09 PM (in response to mikebeargie)Yes, if you could simplify it any. I was a little overloaded by the answer. I am trying to make a calculation for the first field, which let's say is a weekday 2 days prior to the delivery date. Then another field is 1 or two days prior to other field and needs to stay on a week day. Then another field , which all these fields are date fields with calculations, that will show a result a day or two prior to the last field. Does this make since?
I have a delivery date field then a due date field that needs to be 2 days before the delivery field. Then a finishing date field 1 day before the due date. Then an assembly date field that is 2 days before the finishing date field field ect. And I am trying to keep all these calculated dates to fall on Monday through Friday.
Thnx

6. Re: Calendar Calculation to stay in work week
erolst May 5, 2014 5:59 PM (in response to robby@brays.net)robby@brays.net wrote:
…the same thing twice over…
You stated all that in your first post. If you take a closer look at this function, you'll see that that's exactly what it does:
• define the current event by subtracting the number of days it needs to be finished before the previous event (e.g. due = dev  2; see below)
• if the result falls on a weekend, further subtract the number of days necessary to move it to the preceding Friday (which is a tad more complicated and makes the entire beast look so forbidding).
The only thing you may not like is that the result is a list of these dates. (I guess I misunderstood the “stack the dates in the right order” bit of your original post.)
robby@brays.net wrote:
Yes, if you could simplify it any. I was a little overloaded by the answer.
This cannot really be 'simplified'.
It's like this: if you use this calculation in a calculation field, or as an autoenter calculation of a 'normal' field, it just works – you don't have to understand it. But you should know how to implement it.
So, if you want to calculate four different fields …
Start with the calculation…
Let ( [
dev = YourTable::yourDateDeliveryField ;
due = Let ( [ d = dev  2 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) ) ;
finishing = Let ( [ d = due  1 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) ) ;
assembly = Let ( [ d = finishing  1 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) ) ;
etc = Let ( [ d = assembly  1 ; w = DayOfWeek ( d ) ] ; d  Case ( w = 7 ; 1 ; w = 1 ; 2 ) )
] ;
due
)
Copy it, paste it into your dateDue field definition (or autoenter calculation), then replace the part in bold with the actual name of your delivery date field.
Close the field definition (regardless if you use the calculation as calculation field definition or autoenter calculation) to save it. Maybe close the Manage Database dialog.
Open it again, copy the calculation and use it for the three other date fields; each time, substitute the final result line with the expression that matches the current field (finishing, assembly, …don't know what etc is, though )

7. Re: Calendar Calculation to stay in work week
siplus May 6, 2014 5:19 AM (in response to robby@brays.net)Try this one.
I'm calculating some work days in the past in advance, put them in a list and when you ask for example n days backwards you get the nth element of the list, which is guaranteed a work day.
Radu

DAteArray.fmp12.zip 66.4 K


8. Re: Calendar Calculation to stay in work week
robby@brays.net May 6, 2014 5:22 AM (in response to erolst)Thanks so much. I'm gonna give that a whirl. If it works, it's going to help me out big time. Really appreciate it .

9. Re: Calendar Calculation to stay in work week
erolst May 6, 2014 8:02 AM (in response to robby@brays.net)robby@brays.net wrote:
Thanks so much. I'm gonna give that a whirl. If it works, it's going to help me out big time. Really appreciate it .
See here for a sample file that shows two different approaches:
a) a number of fields directly stored in the Projects/Jobs/whatever table
b) a more flexible method using related date records with milestone categories, plus a table with milestone definitions; this facilitates e.g. their display in a calendar module.

10. Re: Calendar Calculation to stay in work week
robby@brays.net May 10, 2014 7:00 AM (in response to erolst)That worked PERFECTLY. Thanks again everyone for all the help. I'd been pulling my hair out trying to get that to work.