7 Replies Latest reply on Dec 10, 2015 3:33 PM by user19752

# calculate the week ending on Friday

How do I calculate the week ending on Friday for the date in a record so I can do a summary report of weekly counts?

I am using this calc for week beginning on Monday:

Let ( dw = DayOfWeek(ED visit date) ; ED visit date - dw + 2 - 7 * (dw = 1 ) )

But I need to compare to another dataset that uses week ending on Friday.

Thanks–

• ###### 1. Re: calculate the week ending on Friday

rubinjm wrote:

How do I calculate the week ending on Friday

So... given a date, determine the next Friday on the calendar? (Or that day, if it's alread a Friday?)

Chris Cain

Extensitech

• ###### 2. Re: calculate the week ending on Friday

Next Friday on the calendar for a given date.

• ###### 3. Re: calculate the week ending on Friday

Let (

[

_date = whatever_date ;

_day = DayofWeek ( _today )

];

Case (

_date = 7 ; _date + 6 ;

_date = 5 ; _date + 1;

_date = 4 ; _date + 2;

_date = 3 ; _date + 3;

_date = 2 ; _date + 4;

_date = 1 ; _date + 5;

_date

)

)

Probably easier or more elegant ways to do this.

• ###### 4. Re: calculate the week ending on Friday

Something doesn't look right with your calc, Chris. However, here is one that will give you the date of the NEXT Friday from any starting date:

Let (

[

today = GetAsDate ( "8/12/2015" )

; dow = DayOfWeek ( today )

; nextFriday = today + 6 + Case ( dow < 6 ; - dow ; dow = 6 ; + 1 ; "" ) // see notes

] ;

nextFriday

)

//NOTE 1: Filemaker designates the days of the week 1 – 7, starting on Sunday; thus Friday = 6

//NOTE 2: The first sum is to add 6 to the starting date ( today + 6 )

// If the starting date is earlier than Friday ( dow < 6 ), then this will take you to a date BEYOND next Friday, so you then have to deduct the starting dow

// If the starting date is itself a Friday ( dow = 6 ), then you must add 1 to the first sum to get the FOLLOWING Friday

// If the starting date is a Saturday, the first sum will give you the date of the next Friday

• ###### 5. Re: calculate the week ending on Friday

yea should have been...

Let (

[

_date = whatever_date ;

_day = DayofWeek ( _today )

];

Case (

_day = 7 ; _date + 6 ;

_day = 5 ; _date + 1;

_day = 4 ; _date + 2;

_day = 3 ; _date + 3;

_day = 2 ; _date + 4;

_day = 1 ; _date + 5;

_date

)

)

• ###### 6. Re: calculate the week ending on Friday

All:

Although not as easy to explain, the following will also work.

Ed Visit Date + Middle ( "5432106" ; DayOfWeek ( Ed Visit Date ) ; 1 )

TSGal

FileMaker, Inc.

• ###### 7. Re: calculate the week ending on Friday

In this thread, op want next Friday on Friday then it should be "5432176".

There is a variation using Choose() function instead of Middle().