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.

[augmented]

Make sure all of your single quotes are ' (straight) and not ’.

[Databaser]

Stephen:

I’m trying to understand how this works

Airtable takes Sunday as the 1ste week day. By deducting 1 day of every day, Sunday becomes Saterday in the same week and Monday becomes Sunay in the same week. I’m using this a lot for my (European) clients where the week starts on Monday and end on Sunday.

DATEADD(date, -1, ‘days’), ‘w’)

So if your date is on Sunday, Monday, … or Saturday, it all will fall in the same week.

Give it a test run and if it works for you, you can choose which formula works best for you.

[Justin_Barrett]

@Stephen Another way to approach this is by comparing the week numbers of the relevant dates using the WEEKNUM() function, which also supports the same “Monday” week-start override. Try this formula:

AND(WEEKNUM(NOW(), "Monday") = WEEKNUM({Enquiry Date/Time}, "Monday"), {Enquiry Date/Time} <= TODAY())

That will output a 1 for any task with a date that’s both on/before today and also in the same calendar week as today. It appears to meet the criteria that you outlined above:

Tomorrow the record dated 8/4 will output a 1, and so on until Sunday. Once Monday hits, that marks the start of a new calendar week, so only next Monday’s tasks will show.

[Stephen]

Yes, it worked with straight quotes… I have no idea how I even managed to do the other quotes…

[Stephen]

Thank you. Very helpful explanation.

[Stephen]

Thanks Justin. There’s now some great solutions in this post. Much appreciated.