8 Replies Latest reply on Mar 21, 2013 9:44 AM by philmodjunk

# How to back out dates based on final release date

### Title

How to back out dates based on final release date

### Post

I'm looking for help on how to formulate a date based on a predetermined release date and a predetermined formula on how long something should take.

For example, in my "Release Deadline" field I have 3/28/13.

I want to populate the "Layout Review" field, knowing that it should take place 3 days before the "Release Deadline", which in this case should be 3/25/13.

To make it more complicated, I would like to also have the weekends taken into account in this formula. So if the "Release Deadline" were 3/27/13 the formula would know that 3/24/13 is a Sunday and move the date to 3/22/13.

• ###### 1. Re: How to back out dates based on final release date

For the first example, we'll ignore the weekend just to show the basic idea:

Release Deadline - 3

will compute a date 3 days before the date in Release Deadline.

There are two additional details:

1) The result type for this calculation must be "Date" and not "Number".
2) Release Deadline must be a field of type date, not text.

Now to adjust that to skip Saturday and Sunday dates:

Release Deadline - 3 - Choose ( DayOfweek ( Release Deadline ) ; 0 ; 1 ; 2 ; 2 ; 2 ; 0 ; 0 ; 0 )

• ###### 2. Re: How to back out dates based on final release date

My formula reads as follows: Table::RELEASE DEADLINE - 3

But this spits back out the same date I have entered in the "Release Deadline" field.

• ###### 3. Re: How to back out dates based on final release date

What kind of Field is Release Deadline?

It must be a field of type date and store a valid date.

I used:

Release Deadline - 3

You've used:

table::Release Deadline - 3

Why do you have table:: in this expression?

if Release Deadline is a field in a different table, the relationship to that other table may be matching to a different record (and thus a different date) than you think.

• ###### 4. Re: How to back out dates based on final release date

If I go to "Manage Database" and scroll to "RELEASE DEADLINE" the type is "date"

Then I click on the "Layout review" field and right click and choose "Conditional Formatting...". Add a new formula and then click on the "Specify" button, double click on the "RELEASE DEADLINE" from the table's list (which autopopulates the "table::" before RELEASE DEADLINE".

• ###### 5. Re: How to back out dates based on final release date

This is not what I meant you to do.

Go to manage | database | fields

Add a new field of type calculation.

Enter the calculation in the specify calculation dialog.

Select "Date" from the result type drop down in the bottom left corner of this dialog box.

• ###### 6. Re: How to back out dates based on final release date

That worked, thank you!

• ###### 7. Re: How to back out dates based on final release date

Sorry, one more thing. Can you explain to me what the "; 0 ; 1 ; 2 ; 2 ; 2 ; 0 ; 0 ; 0" at the end of your formula means?

I'm now trying to do the same thing again but now I need to add 1 day and take the weekend into account.

• ###### 8. Re: How to back out dates based on final release date

Please look up the Choose function in FileMaker help. I've used it to selectively subtract additional days based on the Release Deadline's day of the week. DayOfWeek is another function you can look up in FileMaker help.