Compare Dates in an If Statement (Calculating offset quarter)

I’d like to use an If Statement in a Formula field to check if a date falls within a quarter. Based on the range, I would note Q1, Q2, etc. Is this possible? I haven’t been able to compare a date yet. May not be possible or I just can’t find the correct syntax. It does look like I can trim to capture the month, so that may work. Ideally I could say IF({Request Date}>=1/1/2021 and <= 3/31/2021,“Q1”, Next check)

Hey @BruceB

I think you are on the right track with calculating the month and determining if that falls in the correct range. I created 2 formula columns

  1. calculates month of the date Formula: MONTH({Date 2})
  2. takes month and calculates the Quarter that month is in. Formula: IF({Month}<=3,"Q1",IF(AND({Month}>3,{Month}<=6),"Q2",IF(AND({Month}>6,{Month}<=9),"Q3","Q4")))

This seemed to work for me. Although if you have blank dates, then I guess you’d have to add a 4th IF statement for Q4, I was just lazy and said if its not any of the other Qs then it must be Q4.

Another option is to create a bar chart of your date column, and group by Quarter. That is much simpler, but you also lose the ability to see which exact items are in which quarter.

Hope that helps!

@BruceB,

Another alternative will return the quarter directly:

FORMAT_DATE({Date},"Q")

If you want the “Q” in front:

IF(FORMAT_DATE({Date},"Q")<>"","Q","") & FORMAT_DATE({Date},"Q")

Thanks. That’s a bit cleaner. It looks like it determines Q base on calendar year. What if you want to use your company’s fiscal year? Ours begins on Nov 1.

@BruceB,

That’s a little more complicated…

If you are sure that it will never be blank:

"Q" & ROUNDDOWN( DIVIDE( MOD( MONTH({Date})+1, 12), 3)+1, 0)

Otherwise:

IF({Date}<>"", "Q" & ROUNDDOWN( DIVIDE( MOD( MONTH(IF({Date}<>"",{Date},1))+1,12),3)+1,0),"")

@BruceB,

To answer your original question… probably the simplest way to compare date column values to a constant, put the constants in quotes and YYYY-MM-DD format. Like this:

IF({Date}<"2021-01-15", "Date is Before 1/15/2021", "Date is on or after 1/15/2021")

Hey @BruceB :wave:

It looks like Jcorrell may have answered your question! If you still need more help with the formula, let us know and we’d be happy to continue assisting :slight_smile:

Otherwise, please remember to mark the answer as the “solution” to close out the topic and help future readers find the answer quickly!

Thanks,
Jenna