Fiscal Quarter Issue
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.
Best Answer

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?
Answers

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

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:
="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?

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

Thanks so much for all your help

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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!