Skip to main content

Trying to figure out how to do a search to see if a number falls within 2 fields.

For example a field with Start year and a field with End year.  Lets say on one record my start year was 1978 and my end year was 1986.  I would like to do a search for 1983 and have it retrieve that record.  Can someone point me in the correct direction??

Hello there!

Let’s make sure I understood correctly so I can assist you:

You have three columns:

  • Start year
  • End year
  • Formula field

If the first two fields are single-line text fields containing only the year (and not full dates), you can use the following formula:

IF(AND({Start year}<1983,{End year}>1983), "✅True","❌False")

This will provide the following results:

  • ✅ True if the range includes 1983.
  • ❌ False otherwise.



You could change the "<" and ">" for "<=" and ">=" if you want to include "1983" to your range as well

If you’d like to make this more dynamic and compare different years, I recommend creating a fourth column for the year you want to search. Replace 1983 in the formula with the name of this new field.

Finally, if the fields contain only years and not dates, I suggest converting them to a number type to prevent potential errors (if you haven’t already done so).

Hope this helps!

 

Hey @eightoeight,

@Nina99's instructions will work!
If you'd like to retrieve such records with a more lean/straightforward approach, you can always apply a filter to your view as shown on the image below.

Feel free to reach out via Private Message if you have any other specific need.

Mike, Consultant @ Automatic Nation

Reply


Terms & Conditions