Help with Filter Formula for Custom Range

1

Hello. I’m having trouble coming up with a floating date range formula for the filtering of a table view.

I have a list of rows/items that is ‘Content’ that each have a ‘Publish Date’. Our team has a weekly meeting where we need to view (which I’ve already created) the table of upcoming ‘Content’ based on ‘Publish Date’. I need the view to constantly update to only show items in a three-month window. The view needs to show:

  • 30 days (preferably one month) back from “Today’s Date”, and
  • 60 days (preferably two months) ahead from “Today’s Date”.
    • If possible, extra credit if it can show all the way to the last date of the second month.

For example, “Today’s Date” is 11/29/23. The filter would only show items with a ‘Publish Date’ from 10/30/23 to 1/28/24 (extra credit if the formula goes out to 1/31/24).

Another example, if “Today’s Date” was 12/31/23, the filter would only show items with a ‘Publish Date’ from 11/30/23 (or 12/1/23?) to 2/29/24.

Currently, my custom filter is not static (see red callout box) and I have to go in and manually adjust it every couple of weeks. I need the filter to calculate the above and consistently change based on “Today’s Date”.

I can’t embed my Coda doc due to org policy, but here are some screenshots:

image
image1476×846 136 KB

Please let me know if you need any more data or screenshots to help me with this formula. Thank you!

******Supplemental information: As you can see from the screenshot, I created a ‘Month Name’ column if this helps with the formula. I can also create a “Calendar Year” column, if this is needed for the extra credit above to work. Thanks again!

2

Unfortunately this wont cover your extra credit but hopefully I understood what you were wanting and this helps.

I would create a view of that table and then filter it by the following formula:

[Publish Date]>= Today() - Duration(30) and [Publish Date]< Today() + Duration(60)

[Publish Date] being the autofilled column name

edit: tried to do the extra credit by using the Relative Date to filter by number of months from today, but I’m not sure if it actually works. Doesn’t throw any errors but I don’t know if Coda actually calculates “1 month” in terms of the calendar or a specific # of days

[Publish Date]>= RelativeDate(Today(),-1 ) and [Publish Date]< RelativeDate(Today(),+2 )

1 Like
3

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.