Skip to main content
Solved

Show Records With Date in Current Week

M

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.

Best answer by Justin_Barrett

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

WEEKNUM(NOW()) = WEEKNUM({Next Contact Date})
View original
Did this topic help you find an answer to your question?

5 replies

Justin_Barrett
Forum|alt.badge.img+5

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

WEEKNUM(NOW()) = WEEKNUM({Next Contact Date})
A
T
2 people like this
  • A
    Anthony_Stansal
  • T
    Traci_Franssen
M
  • Matt_Gorman
  • Author
  • Participating Frequently
  • 8 replies
  • June 20, 2020

Thank @Justin_Barrett - I appreciate your help!

Justin_Barrett
1 person likes this
  • Justin_Barrett
    Justin_Barrett
M
  • Matt_Gorman
  • Author
  • Participating Frequently
  • 8 replies
  • June 23, 2020
Justin_Barrett wrote:

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

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

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
Forum|alt.badge.img+5
Matt_Gorman wrote:

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

M
  • Matt_Gorman
  • Author
  • Participating Frequently
  • 8 replies
  • June 23, 2020
Justin_Barrett wrote:

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:

Reply


Most helpful members this week

Terms & Conditions