Formula to return Fiscal Quarter from date?

Platform discussions Special workflows & use cases
,
1

Hello

I’m trying to use a formula column to return the fiscal quarter and year (ours starts in April) , EG: Q1-24, from a date. We will have some blanks in the date column which I’d like to return a blank in the formula column.

For example April 5th 2023 would be Q1-24 and Jan 5th 2024 would be Q4-24.

I’ve found similar answers and tried some of the suggested formulae, but unfortunately run out of intelligence when I try and adapt them to our use case.

I’d be very grateful if someone could help us with this please?

Thanks

2

Hey @DrOboogie,

We just to be sure we are understanding your goal correctly, to confirm, you’d like to output a fiscal year (in the format or Q1-24) based upon a specific date set in the date column - correct? Is it possible to send a screenshot of your current set up? Or alternatively, formulae you have tried to use to achieve this, so we can be sure we take the right approach? :pray:

3

Hi Bianca.

Thanks for replying.

Thats correct Id like to return the fiscal Quarter and Year from our project completion and slippage dates EG: FY23-Q1.

Below is what I have currently and it does do that. It’s just that I’ll have to keep adding to the formula to for each new quarter.

IF({Slip Date}<>“”, IF(AND({Slip Date}>=“2024-01-01”,{Slip Date}<=“2024-03-31”),“FY24-Q4”,IF(AND({Slip Date}>“2023-03-31”,{Slip Date}<=“2023-06-30”),“FY24-Q1”,
IF(AND({Slip Date}>“2023-06-30”,{Slip Date}<=“2023-09-30”),“FY24-Q2”,IF(AND({Slip Date}>“2023-09-30”,{Slip Date}<=“2023-12-31”),“FY24-Q3”)))),“”)

I was hoping there would be a simpler formula to acheive this that I dont have to keep adding to.

Thanks for your help. :slight_smile:

4

@DrOboogie

Try this:
FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}), 9), "Q-YY")

or

IF({Due Date}, 
  "FY" & FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}),9),"YY")
  & "-Q" & FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}),9),"Q")
""

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

2 Likes
5

Hi Jim

Thanks so much for this. Concise and elegant formula. Your a genius. Both options work like a charm but I’ve gone for the 2nd suggestion.

Have a good week both.

Cheers

6

What does the number 9 represent in this formula?

7

The “9” represents the 9 months to offset the Due Date by using the EDATE() function to get the fiscal date. This is specific to this use case.

EDATE
Adds (or subtracts) months to the given date. Returns a serial number representing the calculated date.
Example: FORMAT_DATE(EDATE(“2019-01-20”, 1)) => “Feb 20, 2019”

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

8

Hi,

How do I get this formula to return “FY24/25 - Q3” not just “FY25 - Q3”

Thanks

9

Hello Rebecca! Welcome to the Community. :waving_hand: Great question. Right now, the Formula Column associates a forward or backward slash as a function within the formula. When reading the formula, rather than interpreting the character as part of the text value that you’d like to return, the system registers it as a comparison of values, even if it is included within quotation marks.

As a workaround, our developers recommend including a space at after the forward slash: “FY24/ 25 - Q3”. Can you please confirm whether this helps?

10

@RebeccaWills2205

Try this:

IF({Due Date}, 
  "FY" & FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}),9),"YY") & "/" & SUM(FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}),9),"YY"), 1)
  & "-Q" & FORMAT_DATE(EDATE(FORMAT_DATE({Due Date}),9),"Q"), ""
)

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday