Help With Financial Quarter and Financial Year Formula

Arnau_Lamarca · ‎Mar 08, 2019

Hi there,

I have started using the Blocks on Airtable and i’m in love with them.

I am trying to get a Dashboard with results and compare them with past years and quarters.

To do so i have a ‘Created Day’ column, then another column with a formula which breaks the Quarter and another for the year. Then i Concatenate them and all good and happy days. The issue i’m facing is that I would like to have the Financial Quarter and Financial Year Column.

For the Financial Quarter i kind of have it by doing the below, but i’m sure it could be neater:

IF({Quarter Hidden}=1, “4”)
IF({Quarter Hidden}=2, “1”)
IF({Quarter Hidden}=3, “2”)
IF({Quarter Hidden}=4, “3”)

My main challenge now is to have the formula for the financial year, which runs from April to March, i would like to name it 19/20 for instance. Or the year ahead. So this way i could have my lovely blocks with Financial Year and Quarter.

And lastly concatenate the Financial Quarter with the Financial Year: Q4 - 19/20

Many thanks in advance for your help,
A

Justin_Barrett · ‎Mar 08, 2019

Here’s an alterate version using SWITCH():

SWITCH({Quarter Hidden}, 1, "4", 2, "1", 3, "2", 4, "3")

If I understand the rest of your request, you’re trying to take the date in the {Created Day} field and break that down into Financial Quarter and Financial Year, then build that label you listed. Is that correct? I’m just a little unclear on the source of the date that will be used for those calculations. So for example, if the date in {Created Day} is 5/1/19, that would turn into Q1 - 19/20. Is that right?