5 Replies Latest reply on Mar 16, 2016 12:02 PM by disabled_morkus

# given a date, calculate the first Sunday past two weeks in the future

I'd like to auto enter a start date for approved work on first Sunday after x days from an approval date (today).  Any idea on how to calculate this?

We schedule events on a weekly basis and never have Sunday start days.  I'd like to stack up approvals on an upcoming Sunday so manager can work on scheduling

Thanks!

Martin

• ###### 1. Re: given a date, calculate the first Sunday past two weeks in the future

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.

HTH.  Chris

• ###### 2. Re: given a date, calculate the first Sunday past two weeks in the future

If today is a Sunday (say 1 Jan) do you want the date returned to be 15 Jan or 22 Jan?

• ###### 3. Re: given a date, calculate the first Sunday past two weeks in the future

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

------                             --------------------------------------------------

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

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.

HTH

- m

• ###### 4. Re: given a date, calculate the first Sunday past two weeks in the future

Thank you.  It seemed to work with Table::DateField - DayOfWeek ( 1 ) +15

• ###### 5. Re: given a date, calculate the first Sunday past two weeks in the future

Do not use hard-coded constants.

- m