Date Range Formula

[Jacqui_Egan]

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?

[kuovonne]

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.

[Jacqui_Egan]

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.

[kuovonne]

`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.

[brianfrumberg]

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?

[Meagan_Caesar]

Hi Kuovonne, wondering if you’re able to help me with a similar question. I have a Date/Time field for a list of activities that fall within different school terms and I want to be able to use the Group function to group them by term, so I’ve been trying to figure out a formula that returns the school term based on the Date/Time field.

This is what I’ve been playing around with, but it’s just not working and I think I need to use the IS_BEFORE/AFTER formulas, but I’m not really sure how. I’m in Australia, so have set the timezone, in case that makes a difference. Hopefully what I’m asking is doable. Thanks in advance for any help!

SWITCH(DATETIME_FORMAT({Date & Time}, “DD/MM/YY”),

07/02/22, <01/04/22, “TERM 1”,
19/04/22, <24/06/22, “TERM 2”,
11/07/22, <16/09/22, “TERM 3”,
04/10/22, <09/12/22, “TERM 4”,)

*ETA: not sure why, but the formula hasn’t come across properly when I press ‘Reply.’ The first dates have the ‘greater than’ symbol in front of them.