Calculate quarter from date

I have a Date Ordered column and want to automatically populate a Quarter column based on the date ordered. What is the best way to write this formula?

Best Answers

  • Heather_Verde
    Heather_Verde ✭✭✭✭✭
    edited 07/22/22 Answer ✓

    If you are going by normal annual quarters then this formula should work =IF(MONTH([date]@row) < 4, "1", IF(MONTH([date]@row) < 7, "2", IF(MONTH([date]@row) < 10, "3", "4"))). If you are going for fiscal quarters that vary then you would have to tweak the above and account for the offset of the months (IE: if your fiscal quarters started in Feb - =IF(MONTH([test date]@row) = 1, "4", IF(MONTH([test date]@row) < 5, "1", IF(MONTH([test date]@row) < 8, "2", IF(MONTH([test date]@row) < 11, "3", "4"))) )

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Chris K

    There are several ways to do this. I'll outline one that you can actually reuse easily if you need this on other sheets as well.

    Create a helper sheet called Quarters. You'll want two columns: MonthNumber and Quarter.

    Populate with all twelve month numbers and their quarter.

    Then, to determine the quarter from the Date Ordered column, use an INDEX/MATCH formula, following the prompts to reference another sheet (your new Quarters sheet.)


    =INDEX({Quarters sheet Quarter column range}, MATCH(MONTH([Date Ordered]@row), {Quarters sheet MonthNumber column range}, 0))

    *The bold italics are the references to the Quarters sheet. For the first one, when you click "Reference Another Sheet", navigate to the Quarters sheet, click the header for the Quarter column, and create the range. Repeat for the MonthNumber column for the second reference.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Heather_Verde
    Heather_Verde ✭✭✭✭✭
    edited 07/22/22 Answer ✓

    If you are going by normal annual quarters then this formula should work =IF(MONTH([date]@row) < 4, "1", IF(MONTH([date]@row) < 7, "2", IF(MONTH([date]@row) < 10, "3", "4"))). If you are going for fiscal quarters that vary then you would have to tweak the above and account for the offset of the months (IE: if your fiscal quarters started in Feb - =IF(MONTH([test date]@row) = 1, "4", IF(MONTH([test date]@row) < 5, "1", IF(MONTH([test date]@row) < 8, "2", IF(MONTH([test date]@row) < 11, "3", "4"))) )

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Chris K

    There are several ways to do this. I'll outline one that you can actually reuse easily if you need this on other sheets as well.

    Create a helper sheet called Quarters. You'll want two columns: MonthNumber and Quarter.

    Populate with all twelve month numbers and their quarter.

    Then, to determine the quarter from the Date Ordered column, use an INDEX/MATCH formula, following the prompts to reference another sheet (your new Quarters sheet.)


    =INDEX({Quarters sheet Quarter column range}, MATCH(MONTH([Date Ordered]@row), {Quarters sheet MonthNumber column range}, 0))

    *The bold italics are the references to the Quarters sheet. For the first one, when you click "Reference Another Sheet", navigate to the Quarters sheet, click the header for the Quarter column, and create the range. Repeat for the MonthNumber column for the second reference.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Chris K
    Chris K ✭✭✭

    Thanks everyone! This is very helpful!

  • ilwiny
    ilwiny ✭✭
    edited 01/09/24

    HI - I'm using Jeff's approach and it's working for calculating the month. I'm trying to add the year to the formula, I want it to return Q1-24 or Q4-25, etc.

    This much works Great.=INDEX({Fiscal Year Range 1}, MATCH(MONTH([End Date]@row), {Fiscal Year Range 2}, 0)).

    This is my table.

    This is how I modified the formula.

    =INDEX({Fiscal Year Range 1}, MATCH(MONTH([End Date]@row), {Fiscal Year Range 2}, 0), MATCH(YEAR([End Date]@row), {Fiscal Year Range 4}, 0))

    The End Date is 12/1/24 and it's returning the number 1.

    How do I tell it to return Q1-25?

  • KennyK
    KennyK ✭✭✭

    @ilwiny In Smartsheet you can concatenate strings using "+", unlike Excel which would use the CONCAT function. Here's how I would do that using your Fiscal Year sheet, although my personal preference is Heather's nested if() method.

    This would produce the format "Q1-2025".

    ="Q" + INDEX({Fiscal Year Range 1}, MATCH(MONTH([End Date]@row), {Fiscal Year Range 2}, 0)) + "-" + YEAR([End Date]@row)

    If you only want the last two digits for the year (i.e., Q1-25), you can add the RIGHT function:

    ="Q" + INDEX({Fiscal Year Range 1}, MATCH(MONTH([End Date]@row), {Fiscal Year Range 2}, 0)) + "-" + RIGHT(YEAR([End Date]@row),2)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!