10 Replies Latest reply on May 10, 2014 7:00 AM by robby@brays.net

# Calendar Calculation to stay in work week

Hello,

So I am trying to come up with a formula, that if I have a delivery date, My due date will be 2 days before the delivery date. Then I have 3 more processes that lets say, process 1 needs to be done 1 day before the due date. Process 2 needs to be done 2 days before the due date. Process 3 needs to be done 3 days before the due date ect.

What I am trying to do, is have a calculation for each of the calandar fields to stack the dates in the right order, AND not allow the date to fall on a weekend.

Thanks and I really look forward to anyones help on this.

Robby

• ###### 1. Re: Calendar Calculation to stay in work week

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

I always keep this bookmarked:

http://www.briandunning.com/filemaker-custom-functions/

So I can find things like this when I need it:

http://www.briandunning.com/cf/1509

• ###### 3. Re: Calendar Calculation to stay in work week

Mike Beargie wrote:

I always keep this bookmarked:

http://www.briandunning.com/filemaker-custom-functions/

Right.

So I can find things like this when I need it:

http://www.briandunning.com/cf/1509

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

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

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

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 auto-enter 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 …

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 auto-enter 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 auto-enter 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

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 n-th element of the list, which is guaranteed a work day.

• ###### 8. Re: Calendar Calculation to stay in work week

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

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

That worked PERFECTLY. Thanks again everyone for all the help. I'd been pulling my hair out trying to get that to work.