Skip to main content

Is within the next number of days

  • October 26, 2020
  • 3 replies
  • 72 views
M

I am trying to find a formula to search records for the next 7 days (or 1 week). I tried the formula DATEADD(TONOW({Birthday}), 1, 'week') or even IS_BEFORE( DATEADD(TODAY(), 1, 'weeks'), {Birthday}) to no avail as my records came out from starting the beginning of the year. I want to find records that are within the next 7 days from now as shown on the image.

Anyone has a formula to this?

3 replies

K
  • Kris11
  • Inspiring
  • October 26, 2020

Hi Michael,

I bet their are multiple ways to accomplish this, but here’s the approach I’d start with. Firstly, assuming you need your formula to determine two things:

  1. The number of days between the ‘Birthday’ date field, and the current date.
  2. Is the ‘Birthday’ date field in the future, or the past?

For the first, you can use:
DATETIME_DIFF({Birthday}, TODAY(), 'days')

For the second part:
IS_AFTER({Birthday}, TODAY())

Bringing them together in one formula:

IF(AND(
   DATETIME_DIFF({Birthday}, TODAY(), 'days')<8,
   IS_AFTER({Birthday}, TODAY())
),"Return True","Return False")

Untested, but hopefully it works for you or at least points you in the right direction. Cheers!

ScottWorld
kuovonne
M
M
  • Michael_Harris
  • Author
  • New Participant
  • October 27, 2020

Hi Michael,

I bet their are multiple ways to accomplish this, but here’s the approach I’d start with. Firstly, assuming you need your formula to determine two things:

  1. The number of days between the ‘Birthday’ date field, and the current date.
  2. Is the ‘Birthday’ date field in the future, or the past?

For the first, you can use:
DATETIME_DIFF({Birthday}, TODAY(), 'days')

For the second part:
IS_AFTER({Birthday}, TODAY())

Bringing them together in one formula:

IF(AND(
   DATETIME_DIFF({Birthday}, TODAY(), 'days')<8,
   IS_AFTER({Birthday}, TODAY())
),"Return True","Return False")

Untested, but hopefully it works for you or at least points you in the right direction. Cheers!


In the future. For instance, what birthdays are coming in the next 7 days (or in 1 week when you think about it). Would either filters be the same thing? I tried the filter IS_AFTER({Birthday}, TODAY()), it…almost worked. It does show the next 7 days but one would show the next few weeks away, I need just the next 7 days/1 week.

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • October 27, 2020

In the future. For instance, what birthdays are coming in the next 7 days (or in 1 week when you think about it). Would either filters be the same thing? I tried the filter IS_AFTER({Birthday}, TODAY()), it…almost worked. It does show the next 7 days but one would show the next few weeks away, I need just the next 7 days/1 week.


@Michael_Harris You need to use the entire formula that @Kris provided, not just that one part of it.

Terms & ConditionsAccessibility statement