Forum Discussion

BethanyHudson's avatar
BethanyHudson
Qrew Trainee
7 years ago

Formula from date to quarter

Please HELP!! I've been trying to figure this out for days!
I'm trying to create a formula that will display quarter names (Quarter 1, Quarter 2, Quarter 3, Quarter 4) based on two fields [Meeting Type] and [Meeting Date]. I want the formula to show what quarter it is based only on the Month part of each date. I don't even know if I'll need to use [Meeting Type].

I tried using the If formula with Contains, but no luck.

Here's what I need: 
If [Meeting Date] Contains 12= Quarter 1
If [Meeting date] Contains 03= Quarter 2
If [Meeting Date] Contains 06= Quarter 3
If [Meeting Date] Contains 09= Quarter 4

Which brings me to my next question. Some meetings were held in a different month than the last month in the quarter- I guess it would just be:
If [Meeting date] Contains 03, 02= Quarter 2
Formulas and functions
  • BethanyHudson's avatar
    BethanyHudson
    Qrew Trainee
    7 years ago
    I'm trying a formula like this now:
    If([Meeting Date]>02 AND [Meeting Date]<04 "Quarter 1")
    Still gives me a syntax error message.
    • JackWoods's avatar
      JackWoods
      Qrew Trainee
      5 years ago
      I just extended the formula for each month so I don't have to do an greater than/less than statement.  the formula below worked from me :)

      If(
      Month([Est.Project End Date])=1,"Q1",
      Month([Est.Project End Date])=2,"Q1",
      Month([Est.Project End Date])=3,"Q1",
      Month([Est.Project End Date])=4,"Q2",
      Month([Est.Project End Date])=5,"Q2",
      Month([Est.Project End Date])=6,"Q2",
      Month([Est.Project End Date])=7,"Q3",
      Month([Est.Project End Date])=8,"Q3",
      Month([Est.Project End Date])=9,"Q3",
      Month([Est.Project End Date])=10,"Q4",
      Month([Est.Project End Date])=11,"Q4",
      Month([Est.Project End Date])=12,"Q4",
      "")

      ------------------------------
      Jack Woods
      ------------------------------
  • WladimirLlanos's avatar
    WladimirLlanos
    Qrew Member
    7 years ago
    If((Month(Today())>0 and Month(Today())<4),1,
    If((Month(Today())>3 and Month(Today())<7),2,
    If((Month(Today())>6 and Month(Today())<10),3,
    If((Month(Today())>9 and Month(Today())<13),4,
    0))))

  • ChayceDuncan2's avatar
    ChayceDuncan2
    Qrew Cadet
    7 years ago
    You'll want to use the Month() function in formulas to get the number value of the month itself and then compare to the quarter end - like so

    If(
    Month([Meeting Date])<=3,"Q2",
    Month([Meeting Date])<=6,"Q3",
    Month([Meeting Date])<=9,"Q4",
    Month([Meeting Date])<=12,"Q1",
    "")

    You can use <= to check for the non quarter end months like 2 this way 



    Chayce Duncan | Technical Lead
    (720) 739-1406 | chayceduncan@quandarycg.com
    Quandary Knowledge Base
  • JonathanValvano's avatar
    JonathanValvano
    Quickbase Staff
    2 years ago

    I am a little late to the party.

    This example is using the Simple Project Manager from Exchange, to add an 'Estimated Start Quarter' field to the Projects Table with the format of "year -QX" -> Example: "2023-Q1".   The new field's data type should be a 'Formula - Text'.

    The use the following formula: 

                     Year([Est Start Date]) & " - Q" & Floor((Month([Est Start Date])+2)/3)

    How this works?

    1. Month() from quickbase are values 1-12.
    2. Adding 2 so our return values are 1 - 4.   If we did not do this the quarters returned are 0-3.
    3. Divide by 3 to get the Quarter + Remainder 
    4. Floor()  - Returns the largest integer less than or equal to the number x.  This will remove the Remainder. 

    So when the Est Start Date is in February, the math looks like:

    1. Month() = 2
    2. Add 2 = 4
    3. Divide by 3 = 1.3
    4. Floor(1.3)  = 1

    Happy Building!



    ------------------------------
    Jonathan Valvano
    ------------------------------