Skip to main content
Question

Formula from date to quarter

  • March 14, 2019
  • 0 replies
  • 472 views
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
    This topic has been closed for replies.

    • BethanyHudson
    • Author
    • Registered
    • March 14, 2019
    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.
      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))))

      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

      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
      ------------------------------
      Terms & ConditionsAccessibility statement