+8Hi
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
Best answer by augmented
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!
+18Yes, 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!
+8Yes, 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!
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:
+18Thanks 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:
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!
+19Hi 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!
Wouldn’t this formula also work? :thinking:
IF(DATETIME_FORMAT(DATEADD(date, -1, ‘days’), ‘w’)= DATETIME_FORMAT(DATEADD(TODAY(), -1, ‘days’), ‘w’), “this week”)
+8Hi 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!
Much appreciated. I’ll take a look now.
+8Wouldn’t this formula also work? :thinking:
IF(DATETIME_FORMAT(DATEADD(date, -1, ‘days’), ‘w’)= DATETIME_FORMAT(DATEADD(TODAY(), -1, ‘days’), ‘w’), “this week”)
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.
+8Wouldn’t this formula also work? :thinking:
IF(DATETIME_FORMAT(DATEADD(date, -1, ‘days’), ‘w’)= DATETIME_FORMAT(DATEADD(TODAY(), -1, ‘days’), ‘w’), “this week”)
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.
+8Hi 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!
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’)
+18So 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.
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.
+18I 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’)
Make sure all of your single quotes are ' (straight) and not ’.
+19So 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.
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.
+20@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.
+8Make sure all of your single quotes are ' (straight) and not ’.
Yes, it worked with straight quotes… I have no idea how I even managed to do the other quotes…
+8Airtable 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.
Thank you. Very helpful explanation.
+8@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.
Thanks Justin. There’s now some great solutions in this post. Much appreciated.
+6Took 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
Delivery Week (Step 2)
+1Took 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
Delivery Week (Step 2)
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?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
