Calculate quarter from date

Chris K

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?

Heather_Verde

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

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

Heather_Verde

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

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

Chris K

Thanks everyone! This is very helpful!

ilwiny

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

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

LydiaB1029

@KennyK How would you update Heather's nested if()method to capture the years? Specifically if your fiscal year starts in February. 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"))) )

KPH

Hi @LydiaB1029

If your year starts in February and you want any January dates to have a year that is the previous year, and Feb-Dec to have the current year then you just need:

=IF(MONTH([test date]@row) = 1, YEAR([test date]@row) - 1, YEAR([test date]@row))

This says if the month is January (month 1), return the year of the test date minus 1, if not return the year of the test date.

You can add this to the formula you had for month, with a space between the month number and year like this:

=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"))) ) + " "+IF(MONTH([test date]@row) = 1, YEAR([test date]@row) - 1, YEAR([test date]@row))

If you only want 2 digits for the year rather than 4, you can include only the right most 2 characters by adding this part in bold:

=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")))) + " " + RIGHT(IF(MONTH([test date]@row) = 1, YEAR([test date]@row) - 1, YEAR([test date]@row)), 2)

Let me know how you get on.

LydiaB1029

This works! Thank you for explaining the formula, "This says if the month is January (month 1), return the year of the test date minus 1, if not return the year of the test date." It's helpful for me go forward to understand the logic.

KPH

No problem at all, I'm pleased my explanation made sense.