Fiscal Quarter Issue

Diego Casillas Zaragoza

Hello, I am trying to get the fiscal Quarter and Fiscal year based on a date column.

FOr example, column A has a date and I want column B to return my fiscal quarter and year.

I have no idea how to set that up in smartsheet. The excel formulas I have tried, have not worked in smartsheet. I would love it someone could help me with this.

Genevieve P.

Hi @Diego Casillas Zaragoza

I'm glad that worked for you!

We can add in a statement at the beginning for this one exception, try this:

="Q" + IF(AND(MONTH(Date@row) = 1, DAY(Date@row) = 31), "2FY", IF(OR(MONTH(Date@row) >= 11, MONTH(Date@row) = 1), "1FY", IF(MONTH(Date@row) >= 8, "4FY", IF(MONTH(Date@row) >= 5, "3FY", "2FY")))) + IF(MONTH(Date@row) >= 11, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))

Are there any other exceptions?

Genevieve P.

Hi @Diego Casillas Zaragoza

Here are some other Community posts that had similar questions, let me know if any of this helped!

• Help with formula to populate a quarter (text) based off date field.
• Formula to calculate fiscal quarter
• Formula to assign fiscal quarter not working
• Simpler Fiscal Year and Quarter Formula

Cheers,

Genevieve

Diego Casillas Zaragoza

Im sorry but I am very dense when it comes to formulas. None of these are helping me as I don't understand how formulas work. I guess Im a dummy but I need a more simplified instruction on how to enter my formula

Genevieve P.

Ok no problem at all! Can you post a screen capture of how your sheet is set up with the column names? (But block out sensitive data).

Diego Casillas Zaragoza

Genevieve P.

The way I would do this is to find the Month of the Date column (using the MONTH function), then based on what number it returns use an IF statement to say "Q1", etc.

The end formula in this post is the one I'd replicate, here. The column name for Date is the exact same, too!

The formula is specific for these quarters:

Q1: Oct, Nov, Dec (10, 11, 12)

Q2: Jan, Feb, Mar (1, 2, 3)

Q3: Apr, May, Jun (4, 5, 6)

Q4: July, Aug, Sept (7, 8, 9)

It adds a Q at the beginning. Then it sees if the MONTH is greater than 10 (Oct, Nov, Dec), it will say Q1FY.... (the end of the formula adds the year).

="Q" + IF(MONTH(Date@row) >= 10, "1FY",

Otherwise,

then IF it's greater than or = to 7 (July, Aug, Sept), it's Q4

IF(MONTH(Date@row) >= 7, "4FY",

Otherwise, if it's not greater than 7, then IF it's greater than or = to 4 (Apr, May, Jun), it's Q3

Otherwise, it has to be Q2 as the only option left.

IF(MONTH(Date@row) >= 4, "3FY", "2FY")))

Now to add the year! If the month is greater than or = to 10, then we have to add 1 to the Year in order to find the FY.

IF(MONTH(Date@row) >= 10, RIGHT(INT(YEAR(Date@row) + 1), 2), 

But if it's not greater than or = to 10, we just need to grab the end of the year:

RIGHT(YEAR(Date@row), 2))

Try this full formula:

="Q" + IF(MONTH(Date@row) >= 10, "1FY", IF(MONTH(Date@row) >= 7, "4FY", IF(MONTH(Date@row) >= 4, "3FY", "2FY"))) + IF(MONTH(Date@row) >= 10, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))

Let me know if this makes sense!

Cheers,

Genevieve

Diego Casillas Zaragoza

I got unparseable. My Fiscal year started on November 1st if that helps

Genevieve P.

Hi @Diego Casillas Zaragoza

Thank you for clarifying that your quarters are different. This means that you are looking for the following, correct?

Q1: 11, 12, 1

Q2: 2, 3, 4

Q3: 5, 6, 7

Q4: 8, 9, 10

If so, we do need to make a little adjustment for each of the quarter statements. Q1 can include 1, so we'll have to add in an OR statement at the beginning and adjust the other numbers.

="Q" + IF(OR(MONTH(Date@row) >= 11, MONTH(Date@row) = 1), "1FY", IF(MONTH(Date@row) >= 8, "4FY", IF(MONTH(Date@row) >= 5, "3FY", "2FY"))) + IF(MONTH(Date@row) >= 11, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))

You should be able to copy/paste the formula at the end, as long as the Date column is titled "Date", otherwise you may need to adjust the column name in the formula.

If you get an error, can you post a screen capture with the formula open in the cell, showing if it's highlighting the correct Date column or not?

Diego Casillas Zaragoza

This actually worked but Jan 31st is part of Q2 and its marking it as Q1

Genevieve P.

Hi @Diego Casillas Zaragoza

I'm glad that worked for you!

We can add in a statement at the beginning for this one exception, try this:

="Q" + IF(AND(MONTH(Date@row) = 1, DAY(Date@row) = 31), "2FY", IF(OR(MONTH(Date@row) >= 11, MONTH(Date@row) = 1), "1FY", IF(MONTH(Date@row) >= 8, "4FY", IF(MONTH(Date@row) >= 5, "3FY", "2FY")))) + IF(MONTH(Date@row) >= 11, RIGHT(INT(YEAR(Date@row) + 1), 2), RIGHT(YEAR(Date@row), 2))

Are there any other exceptions?

Diego Casillas Zaragoza

its still returning Q1 for Jan 31st :(

Genevieve P.

Hi @Diego Casillas Zaragoza

Can you post a screen capture with the formula open in the cell, showing the Date cell you're referencing?

Diego Casillas Zaragoza

Im so sorry! I was clicking December 31st the entire time! I have you working overtime on this for no reason! I apologize profusely!

This actually works perfectly! Thank you so much.

I have another problem I'd like solved though, can you help or do I start another help ticket?

Genevieve P.

Haha no problem at all! I'm so glad the final version works for you.

If the question is on a different topic, I would recommend creating a whole new Question for it in the Community. That way if other users are searching for a similar topic, it will be easier to find your separate question and member responses. It will also make sure the question is in the right category! 🙂

Cheers,

Genevieve

Diego Casillas Zaragoza

Thanks so much for all your help

tgnockles

Our business has the same quarter dates. This post helped me out tremendously. @Genevieve P. is there a possible way to edit your formula to have the quarter dates fall in these specific date times? Thanks!

Q1: 11/6 - 2/5.

Q2: 2/6 - 5/5

Q3: 5/6 - 8/5

Q4: 8/6 - 11/5