Help

Re: Show Records With Date in Current Week

Solved
Jump to Solution
3774 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_Gorman
5 - Automation Enthusiast
5 - Automation Enthusiast

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. Screen Shot 2020-06-20 at 12.15.49 PM

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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

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

See Solution in Thread

5 Replies 5
Justin_Barrett
18 - Pluto
18 - Pluto

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
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank @Justin_Barrett - I appreciate your help!

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?

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().

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