Date Range Formula

I’m trying to create a formula field to denote project “wave” based on a date range. So, if the target completion date is between 3/1/2020 and 5/30/2020, this would be “Wave 1”, if the target completion date is between 6/1/2020 and 8/31/2020 this would be “Wave 2”, etc. I’ve made a few different attempts, but it doesn’t like my formula. Any ideas?

Can you post the formulas that you have tried? Are your date ranges based on static dates or dates in other fields? Could there be a date before 3/1/2020 or after 8/31/2020. It is easier to help if we have more information.

There are many ways to create a formula field that does what you want. You will need to combine multiple functions together. You can see the formula field reference for more info.

For example, if you only care if the date is before/after 6/1/2020 you could use a very simple formula:

IF( 
  IS_BEFORE(
    {completion date}, 
    DATETIME_PARSE("6/1/2020", "M/D/YYYY")
  )
  "Wave 1",
  "Wave 2"
)

If you have more date ranges or the date range are stored in fields, the formula would look very different.

Thank you so much for your reply, Kuovonne. Unfortunately, I didn’t save the formulas. I essentially have one date column for target end date of the project. If that date lands in March, April or May, I want a formula that will tag it as Wave 1, and so on for subsequent quarters. So for Wave 1, I care about the date being after 3/1/2020 and before 5/30/2020.

`Thanks for providing more info. As I said before, there are many ways to calculate if a date is in a range. If your ranges are typical calendar quarters, the easiest way is to calculate the quarter based on the month.

SWITCH(MONTH({Date}),
  1, "Quarter 1",
  2, "Quarter 1",
  3, "Quarter 1",
  4, "Quarter 2",
  5, "Quarter 2",
  6, "Quarter 2",
  7, "Quarter 3",
  8, "Quarter 3",
  9, "Quarter 3",
  10, "Quarter 4",
  11, "Quarter 4",
  12, "Quarter 4"
)

If your date ranges are more complex, or setup in a different way, let us know and we can see if a different formula would work better.

Hi there! My requirements for date range are more complex in that they include the day. So, I need “Month 1” to be 9/6/21 to 10/5/21 and “Month 2” to be 10/6/21 to 11/5/21, and so on. How would I go about doing that in Airtable?