5 Replies Latest reply on Feb 9, 2015 10:49 AM by philmodjunk

# How to calculate last day of the week?

### Title

How to calculate last day of the week?

### Post

I need a formula to calculate the last day of the week which is a saturday.

I want to enter a date and it must give me the last date of the week in a date format. If the date I enter is a saturday it must give me that date as it's the last date of the week.

For example.

If I enter todays date "2015/02/09" The result must be "2015/02/14"

If I enter "2015/02/14" The result must be "2015/02/14"

If I enter "2015/02/05". The result must be "2015/02/07"

Thank you

• ###### 1. Re: How to calculate last day of the week?

Date + 7 - DayOfWeek(Date)

• ###### 2. Re: How to calculate last day of the week?

Thanks Timothy but your calculation doesn't work.

If I change the 7 to a 6 it sometimes works for example: Date + 6 - DayOfWeek(Date)

Also the result is not giving me a date result? GetAsDate(Date + 6 - DayOfWeek(Date)) Also doesn't work.

• ###### 3. Re: How to calculate last day of the week?

The expression posted by Timothy should work. It's a variation of the one I was about to suggest.

The field date must be a field defined in your database as a field of type date.

Your calculation field's result type must specify a date result type.

• ###### 4. Re: How to calculate last day of the week?

For example, 2/9/2015 + 7 - DayOfWeek(2/9/2015) (US format) won't work; Date(2; 9; 2015) + 7 - DayOfWeek(Date(2; 9; 2015)) will.

• ###### 5. Re: How to calculate last day of the week?

That is correct. Using an explicit date in your calculation makes no sense anyway as you'd always be computing the same result in every record of your table.

To FileMaker 2/9/2015 is the math expression 2 divided by 9 divided by 2015. It doesn't evaluate as a date.

GetAsDate ( "2/9/2015" ) does work if that quoted text is consistent with your systems locality systems. (Some places in the world specify DD/MM/YYYY where we in the US specify MM/DD/YYYY and this can be controlled by settings in FileMaker and your Computer.)

But it has the same limitation as I have already stated--you are going to get the same result in any record so it makes no sense to set up in that fashion--you might as well just type the result in as GetAsDate ( "2/14/2015" ).