Hi Martin. Try this calc:
Table::DateField - DayOfWeek ( Table::DateField ) +15.
This will give you the Sunday that is two weeks after the Sunday that has just passed.
If today is a Sunday (say 1 Jan) do you want the date returned to be 15 Jan or 22 Jan?
Before trying to do anything in FileMaker (or other platform), the way I would do this would be to have a simple matrix of weekdays with what day should be calculated (maybe include sample dates as well as the day too):
Day Date To Be Calculated for Scheduling
Once you fill in these dates, it would become obvious how to handle the date math.
Today, Wednesday, for example, DayOfWeek ( Get(CurrentDate) ) returns 4.
So, DayOfWeek ( GET(CurrentDate)+4) would be Sunday returns 1.
I would never use hard coded "magic number" values in any date math so the +4 was just for illustration. If you need to use a hard-coded date offset, it should be in a constants table with a self-documenting field name.
In any case, once you have the matrix filled out above (and added as a comment to the script, custom function or whatever, so you will remember what you were thinking when you look at this code again six months from now....), you'd have the basis to do the computation correctly and quickly.
So, at the risk of being repetitive, my advise is to map out what you want first (stay away from FileMaker or other program encouraging you to "jump in") say on paper. Get the "algorithm" done first, albeit in this case a simple one. Then, you have a much better chance of a quick and correct implementation with less frustration. From studies done (See MS Press Code Complete 2, for example) taking this time to figure things out first actually makes a solution happen FASTER, not slower, counter to what it may seem like or what others say.
The computer should be the easy part unless you're tying to figure out your logic as you go.
Thank you. It seemed to work with Table::DateField - DayOfWeek ( 1 ) +15
Do not use hard-coded constants.