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]

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.

[Matteo_Cossu3]

Took me a while but if you're looking to label This Week, Next Week, etc from weeks starting Sunday I came up with this. Someone please consolidate in one field.

Weekday -1

(WEEKDAY({Delivery Date})*-1)

Delivery Week (Step 1)

DATEADD({Delivery Date},{weekday *-1}, 'days')

Delivery Week (Step 2)

DATEADD({Delivery Week (Step 1)},1, 'days')

target date today

DATETIME_DIFF(TODAY(), {Delivery Week (Step 2)}, 'days')

Delivery Week

SWITCH({target date today},

-42, '⏭️ 6 Weeks Out',

-41, '⏭️ 6 Weeks Out',

-40, '⏭️ 6 Weeks Out',

-39, '⏭️ 6 Weeks Out',

-38, '⏭️ 6 Weeks Out',

-37, '⏭️ 6 Weeks Out',

-36, '⏭️ 6 Weeks Out',

-35, '⏭️ 5 Weeks Out',

-34, '⏭️ 5 Weeks Out',

-33, '⏭️ 5 Weeks Out',

-32, '⏭️ 5 Weeks Out',

-31, '⏭️ 5 Weeks Out',

-30, '⏭️ 5 Weeks Out',

-29, '⏭️ 5 Weeks Out',

-28, '⏭️ 4 Weeks Out',

-27, '⏭️ 4 Weeks Out',

-26, '⏭️ 4 Weeks Out',

-25, '⏭️ 4 Weeks Out',

-24, '⏭️ 4 Weeks Out',

-23, '⏭️ 4 Weeks Out',

-22, '⏭️ 4 Weeks Out',

-21, '⏭️ 3 Weeks Out',

-20, '⏭️ 3 Weeks Out',

-19, '⏭️ 3 Weeks Out',

-18, '⏭️ 3 Weeks Out',

-17, '⏭️ 3 Weeks Out',

-16, '⏭️ 3 Weeks Out',

-15, '⏭️ 3 Weeks Out',

-14, '⏭️ 2 Weeks Out',

-13, '⏭️ 2 Weeks Out',

-12, '⏭️ 2 Weeks Out',

-11, '⏭️ 2 Weeks Out',

-10, '⏭️ 2 Weeks Out',

-9, '⏭️ 2 Weeks Out',

-8, '⏭️ 2 Weeks Out',

-7, '⏭️ Next Week',

-6, '⏭️ Next Week',

-5, '⏭️ Next Week',

-4, '⏭️ Next Week',

-3, '⏭️ Next Week',

-2, '⏭️ Next Week',

-1, '⏭️ Next Week',

0, '▶️ This Week',

1, '▶️ This Week',

2, '▶️ This Week',

3, '▶️ This Week',

4, '▶️ This Week',

5, '▶️ This Week',

6, '▶️ This Week',

7, '⏮️ Last Week',

8, '⏮️ Last Week',

9, '⏮️ Last Week',

10, '⏮️ Last Week',

11, '⏮️ Last Week',

12, '⏮️ Last Week',

13, '⏮️ Last Week',

14, '⏮️ 2 Weeks Ago',

15, '⏮️ 2 Weeks Ago',

16, '⏮️ 2 Weeks Ago',

17, '⏮️ 2 Weeks Ago',

18, '⏮️ 2 Weeks Ago',

19, '⏮️ 2 Weeks Ago',

20, '⏮️ 2 Weeks Ago',

21, '⏮️ 3 Weeks Ago',

22, '⏮️ 3 Weeks Ago',

23, '⏮️ 3 Weeks Ago',

24, '⏮️ 3 Weeks Ago',

25, '⏮️ 3 Weeks Ago',

26, '⏮️ 3 Weeks Ago',

27, '⏮️ 3 Weeks Ago',

28, '⏮️ 4 Weeks Ago',

29, '⏮️ 4 Weeks Ago',

30, '⏮️ 4 Weeks Ago',

31, '⏮️ 4 Weeks Ago',

32, '⏮️ 4 Weeks Ago',

33, '⏮️ 4 Weeks Ago',

34, '⏮️ 4 Weeks Ago'

)

[gaddeo]

Hi 
Thanks for this formula.  I tried it out Saturday and it was working perfectly. Today, with no changes on my side, it has stopped.  Any ideas why that had occurred?