Jump to content

Recommended Posts

rob    2
Posted (edited)

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 by rob

Share this post


Link to post
Share on other sites
siroos12    9

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.

 

 

 

Share this post


Link to post
Share on other sites
comment    1,372

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).

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.