cancel
Showing results for 
Search instead for 
Did you mean: 

Quarter formula

nvinay091
Deputy Chef I
Deputy Chef I

How to get which quarter from the date format 2022-01-31
Is there any formula to get, quarters ( Q1, Q2, Q3,Q4) from the date

6 REPLIES 6

jblanchett
Deputy Chef III
Deputy Chef III

I never found a great way to do this. Best options I found were using strftime to get month from date then using a hash mapping to get quarter (then append year). Lookup table is an option as well. If you are working with a system that has a table of fiscal quarters already with start/end dates, you can search that system with date parameters (e.g. NetSuite).

You could also divide the month by 3 and add 1 instead of using a map or lookup table. I think you'd have to convert the output from strftime to an integer with to_i and then convert back, so the formula may get complicated, but could save building a lookup.

kale-bogdanovs
Workato employee
Workato employee

Hey there!


One way to do this would be to create a lookup table mapping months to quarters. This works even if you have a non-standard financial year. Here's an example table:



You can then use the lookup table action to get the quarter from a date.

Note that to do this, i've used formulas to:

* change the date to a string format

* split it by the "-" character to give a list that will look like ["2021", "04", "12"]

* selected the second element of the list, which will be the month.


The returned quarter value is will now be available in the recipe data widget and I can map it wherever I need.



Hope this helps.

jeff-allen
Deputy Chef I
Deputy Chef I

You can consider using a Formula that uses conditions based on the Month value of the date. It is possible to string Ternary logic together to get the quarter output from a formula.