Help

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

Solved
Jump to Solution
3542 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Stephen
6 - Interface Innovator
6 - Interface Innovator

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

17 Replies 17

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

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.

@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:

Screen Shot 2021-08-03 at 7.21.21 AM

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.

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

Thank you. Very helpful explanation.

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

Matteo_Cossu3
6 - Interface Innovator
6 - Interface Innovator

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'


)

 

 

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?