Fiscal Quarter Issue

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?

All comments

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

Cheers,

Genevieve

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

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).

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

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

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?

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

I'm glad that worked for you!

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

Are there any other exceptions?

its still returning Q1 for Jan 31st :(

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

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?

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