Find prior Wednesday

[rob]

Okay, our sales reps run in a weekly cycle starting on Wednesday.  I need, regardless what date it is, to find all records from the Wednesday before through the next Tuesday.   Oh, and only the current year except if the search is done early in January, it needs to go into prior year.  I tried many things but still fail in the find but it fails. 

Added - I think it good to have a day of week in Admin in case this changes to different day.  Then the find would use that day of week to jump back and ahead and find the records we need.  Week of year seems to fail for me. I searched but still need help.

Edited July 12, 2017 by rob

[siroos12]

You may achieve that using get(CurrentDate) and DayOfWeek( get (CurrentDate))

 

the formula would be something like this:

get(CurrentDate)-If ( DayOfWeek( get (CurrentDate))≥ 4;DayOfWeek( get (CurrentDate))-4;DayOfWeek( get (CurrentDate))-4+7 )

 

This would return the date of the last Wednesday.

 

 

 

[comment]

You can calculate the date of last Wednesday using =

Get ( CurrentDate ) - Mod ( Get ( CurrentDate ) - 3 ; 7 )

Note that this will return the current date when the calculation is performed on a Wednesday.

 

I am afraid I did not get this part:

17 minutes ago, rob said:

Oh, and only the current year except if the search is done early in January, it needs to go into prior year.  I

The calculation looks only at the weekly cycle and ignores year boundaries.

 

19 minutes ago, rob said:

I think it good to have a day of week in Admin in case this changes to different day.

If you like,  you can use =

Get ( CurrentDate ) - Mod ( Get ( CurrentDate ) - Weekday + 1 ; 7 )

where Weekday is a number between 1 (Sunday) and 7 (Saturday).