Date Column - 'Filter' by "this week' - is it possible?

[Stephen]

Hi

Does anyone know of a way to ‘Filter’ a ‘Date’ column by something similar to “this week”?

Basically, I’m just trying to create a Grid View that shows - via a Filter - all enquiries that have been received ‘this week’ (which is from Monday through to Sunday of the week I’m looking/accessing the View in).

Any help would be greatly appreciated.

Thank you

Stephen

[augmented · Accepted solution]

Yes, it’s possible, although not as straightforward as you would hope. You need to create a new formula field to determine if your date field is within your week. Below, I used my date field, dt. This formula uses a Sunday to Saturday week, but you can play with the arguments to WEEKDAY to change that to Monday start.

Then you filter on this formula field being “Yes”. Good luck!

[Stephen]

Thanks so much for this. I have entered the formula and it does work as described but admittedly it’s frying my brain as to how I actually get this to work for a Monday to Sunday work week :flushed:

[augmented]

Hi Stephen. You need to change the WEEKDAY functions to have an extra argument, 'Monday'.

Like so…

IF(AND(IS_AFTER(dt, DATEADD(TODAY(),-1*(WEEKDAY(TODAY(),'Monday')+1)....

Do that in all WEEKDAY functions. Let me know if it does what you want. Cheers!

[Databaser]

Wouldn’t this formula also work? :thinking:

IF(DATETIME_FORMAT(DATEADD(date, -1, ‘days’), ‘w’)= DATETIME_FORMAT(DATEADD(TODAY(), -1, ‘days’), ‘w’), “this week”)

[Stephen]

Much appreciated. I’ll take a look now.

[Stephen]

Thanks for your input on this. Hopefully, this chat will help a lot of people out there that just need to do this one simple thing. Much appreciated.

[Stephen]

So just a further enquiry to this, as I’m a major novice to formulas (but a fast learner), I’m trying to understand how this works; would it still consider “this week” as being Monday to Sunday, regardless of what day I looked at the View?

So for example if I view on a Tuesday it should only bring back those tasks that have occurred on Monday and Tuesday, whereas if I looked at this View on a Sunday it would bring back all those tasks that have appeared on Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday.

[Stephen]

I must be doing something wrong as it’s rejecting the formula with:

IF(AND(IS_AFTER({Enquiry Date/Time},DATEADD(TODAY(),-1*(WEEKDAY(TODAY(),‘Monday’)+1),‘day’)),IS_BEFORE({Enquiry Date/Time},DATEADD(TODAY(),7-WEEKDAY(TODAY(),‘Monday’),‘day’))),‘Yes’,'No’)

[augmented]

My formula would bring in everything for the current week, no matter what day of the week it currently is or what day of the week the record has.