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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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?

«1

Answers

  • 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.
    Genevieve P. Employee Admin

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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?

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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?

  • its still returning Q1 for Jan 31st :(

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Diego Casillas Zaragoza

    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?

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!