6 Replies Latest reply on Nov 28, 2016 10:00 AM by okramis

# Offsetting dayofweek

I am trying to run a search for next week (Monday through Sunday) but since Filemaker starts the week on Sunday (day 1), I am having difficulty getting the results when running the script on Sundays. This is my calculation, but I am uncertain how to put in the offset. Any help would be awesome! Thanks.

( Get ( CurrentDate ) – DayOfWeek ( Get ( CurrentDate ) ) + 9 ) & “…” & ( Get ( CurrentDate ) + 15 – DayOfWeek ( Get ( CurrentDate ) ) )

• ###### 1. Re: Offsetting dayofweek

Hi,

here's a custom function that returns the date of the last day of the week.  This one has been adjusted for the Monday-Sunday week.

Use this to find the last day of the week, then subtract seven six for the first day of the week.

• ###### 2. Re: Offsetting dayofweek

Use a Let statement to make it easy to set out the different components:

```Let (
[
firstDayOfWeek = 2            /* Sun = 1, Mon = 2, etc */
; daysUntilNextWeek =  ( 7 + firstDayOfWeek ) - dayofweek( aDate )
; NextWeekStart = aDate + daysUntilNextWeek
; NextWeekEnd = NextWeekStart + 6
];

getastext( NextWeekStart ) & "..." & getastext( NextWeekEnd )

)
```
• ###### 3. Re: Offsetting dayofweek

here's an other version:

Let ( [

_date = Get ( CurrentDate ) + 7 //date in 1 week

; _weekday = Choose ( DayOfWeek ( _date ) - 1 ; 6 ; 0 ; 1 ; 2 ; 3 ; 4 ; 5 ) //weekday cero-based (6 = sunday)

; _start = _date - _weekday //if today is sunday: _date - 6 = tomorrow

; _end = _start + 6

] ;

_start & "…" & _end

)

best regards

Otmar

• ###### 4. Re: Offsetting dayofweek

well spotted okramis, the end of week is six days forward, not seven

• ###### 5. Re: Offsetting dayofweek

Thanks Otmar and everyone.

Otmar - would that completely replace my calculation? Just want to be sure I understand... your calculation will return next week (with the week starting Monday and ending sundae).

Thanks again. This forum is always helpful!

• ###### 6. Re: Offsetting dayofweek

Yes, it should work as intended. Just put it in the data viewer and set the _date to several test-dates (leave the + 7 behind).

If you define the start of next week if lets say today is sunday as the following day means next day, the formula should be correct, if it should be a week later, just replace "+ 7" with "+ 14".

best regards

Otmar