6 Replies Latest reply on Jun 22, 2010 8:37 AM by Pam

# Exclude Friday from calculated date

### Title

Exclude Friday from calculated date

### Post

I am a novice user of FileMaker (v Pro 10) on a Mac. (have used neither before).

I need to calculate a date 10 days from 'start date', which is a field in a table. If the result is a Friday, then the calculation should be 'start date' + 11. Tried to do this by means of an if statement, but can't figure out the correct wording to exclude Fridays.

I am in Dubai in the UAE, so unsure if Friday is day 6 or not.

Would be grateful for any assistance, I am under pressure to get the DB up and running.

Thanks P

• ###### 1. Re: Exclude Friday from calculated date

Hi

try this one:

```Let(
end = start date + 10 ;
Case(
DayOfweek( end ) = 6 ; end + 1 ;
end
)
)```

that can be simplified to:

`start date + 10 + ( DayOfWeek( start date + 10 ) = 6 )`

• ###### 2. Re: Exclude Friday from calculated date

HI Daniele,

Nothing wrong with your English, but  obviously something wrong with my brain.

I tried this:

Let (Services::ServiceDate = Contracts::StartDate + 10; Case ( DayOfWeek (Services::ServiceDate) = 6, Services::ServiceDate + 1 ; Contracts::StartDate + 10 )))

I get an error message at :: A number, text constant, field name or "(" is expected here.

I am working with fields from 2 different tables. T Services F ServiceDate and T Contracts F StartDate

What to do?

• ###### 3. Re: Exclude Friday from calculated date

A field name can't be a variable name !

Try with:

Let ( E = Contracts::StartDate + 10 ; E + ( DayOfWeek ( E ) = 6 ) )

The field holding this calculation must be into the Contracts table.

• ###### 4. Re: Exclude Friday from calculated date

Hi Daniele,

The field I am trying to calculate sits in the 'Services' table. The field [services].[service date] is calculated 10 days from the field [contract].[start date], which is in the 'Contracts' table. As Friday is the 'day off' here, therefore, if the 10th day falls on a Friday, the service must be the following day.

Would this mean that the proposed solution would be ineffective?

Sorry to be such a pest, your kind assistance is much appreciated.

• ###### 5. Re: Exclude Friday from calculated date

Pam wrote:

The field I am trying to calculate sits in the 'Services' table.

Why it can't stay in the "Contracts table ?

• ###### 6. Re: Exclude Friday from calculated date

Was just the way I had set up the tables.

However, I am pleased to say that you put me on the right track.  I now have a script that takes the service date, calculated from the contracts table, checks to see if the result is a friday, and if so, changes it.