Show Records With Date in Current Week

[Matt_Gorman]

I have a table in which I track each time I contact a client. I also have a formula that uses the date contacted and cycle frequency to determine the next date to connect with the client.

I also have a view that filters the view to only show future “next contact date.” I would like to create a way to show in this view only the clients I need to contact within the current 7 DAY week.

I can’t come up with a formula, and the filter options for the view don’t provide a way to do this.

I was thinking a separate column with a formula that would only populate if the “next contact date” column had a date for the current 7 DAY week. Maybe a checkmark and the view can be filtered if a record has a checkmark.

I’m stumped and would love some thoughts.

[Justin_Barrett · Accepted solution]

This formula will work. You would then filter on the formula field where it outputs a 1.

WEEKNUM(NOW()) = WEEKNUM({Next Contact Date})

[Matt_Gorman]

Thank @Justin_Barrett - I appreciate your help!

[Matt_Gorman]

Justin, I have a follow-up question. Right now if there is not a date in both fields it returns NaN. How can I modify the formula to return an empty result and nothing fill the cell until both fields have dates? Is there an IF statement, or an additional string I need to include?

[Justin_Barrett]

Matt_Gorman:

How can I modify the formula to return an empty result and nothing fill the cell until both fields have dates?

Both fields? There’s only one field in your formula at the moment. Here’s the mod to check for the presence of the date, and only run the comparison if it’s there:

IF({Next Contact Date}, WEEKNUM(NOW()) = WEEKNUM({Next Contact Date}))

Any place you need to make a simple true/false comparison, the field alone will effectively return True if it contains anything, and False if it contains nothing. The only exception to this is a number field, where a 0 will be seen as false. If that comes up, you’ll need to compare against BLANK().

[Matt_Gorman]

@Justin_Barrett I goofed and put this in the wrong thread *facepalm :slightly_smiling_face: