# Formula to capture Quarter

Options
edited 09/08/22

Good Day!

I am struggling with a formula.

I would like the formula to identify the quarter within which the project will be delivered and fill the column Quarter. This information will help our finance department plan revenue. I tried the nested IF function below, and I had the ‘’UNPARSEABLE error message.

=IF([Date Livraison Estimée]@row = " ", " To be planned", IF([Delivery date]@row > DATE(22, 5, 31), "Q1-2022", IF([Delivery date]@row < DATE(22, 9, 1), "Q1-2022")), IF(([Delivery date]@row > DATE(22, 8, 31), "Q2-2022", IF([Delivery date]@row < DATE(22, 12, 1), "Q2-2022"), IF(([Delivery date]@row > DATE(22, 11, 30), "Q3-2022", IF([Delivery date]@row < DATE(23, 3, 1), "Q3-2022"), IF(([Delivery date]@row > DATE(23, 2, 28), "Q4-2022", IF([Delivery date]@row < DATE(23, 6, 1), "Q4-2022"))))))))

Is there a better way to solve this problem? Our fiscal year goes from June 1st through May 31 of the following year.

Cheers,

Claude

• ✭✭✭✭✭✭
Options

Fat fingers again. Should have been a minus 1. My apologies.

=IF([Delivery Date]@row = "", "To Be Planned", "Q" + IF(OR(MONTH([Delivery Date]@row) = 12, MONTH([Delivery Date]@row)<= 2), "3", IF(MONTH([Delivery Date]@row)<= 5, "4", IF(MONTH([Delivery Date]@row)<= 8, "1", "2"))) + "-" + (YEAR([Delivery Date]@row) + IF(MONTH([Delivery Date]@row)<= 5, -1, 0)))

• ✭✭✭✭✭✭
Options

Try this:

=IF([Delivery Date]@row = "", "To Be Planned", "Q" + IF(OR(MONTH([Delivery Date]@row) = 12, MONTH([Delivery Date]@row)<= 2), "3", IF(MONTH([Delivery Date]@row)<= 5, "4", IF(MONTH([Delivery Date]@row)<= 8, "1", "2"))) + "-" + YEAR([Delivery Date]@row) + "" + IF(MONTH([Delivery Date]@row)<= 5, 1, 0))

• Options

Good Day M. Newcome,

An extra digit is added to he result as you can see below. How can I get rid of that?

Claude

• ✭✭✭✭✭✭
Options

Sorry. Got a little ahead of myself.

=IF([Delivery Date]@row = "", "To Be Planned", "Q" + IF(OR(MONTH([Delivery Date]@row) = 12, MONTH([Delivery Date]@row)<= 2), "3", IF(MONTH([Delivery Date]@row)<= 5, "4", IF(MONTH([Delivery Date]@row)<= 8, "1", "2"))) + "-" + (YEAR([Delivery Date]@row) + IF(MONTH([Delivery Date]@row)<= 5, 1, 0)))

• Options

Good Day M. Newcome,

Please find attached the results. Our Fiscal year goes from June 1 through May 31 of the following calendar Year. For example, our Year 2022 is June 1st 2022 through May 31, 2023.

Q1-2022 goes from June 1st to August 31 2022;

Q2-2022 goes from Sept. 1 to Nov. 30 2022

Q3-2022 goes from Dec. 1st 2022 to Feb. 28 2023

Q4-2022 goes from March 1st to May 31 2023

Claude

• ✭✭✭✭✭✭
Options

Fat fingers again. Should have been a minus 1. My apologies.

=IF([Delivery Date]@row = "", "To Be Planned", "Q" + IF(OR(MONTH([Delivery Date]@row) = 12, MONTH([Delivery Date]@row)<= 2), "3", IF(MONTH([Delivery Date]@row)<= 5, "4", IF(MONTH([Delivery Date]@row)<= 8, "1", "2"))) + "-" + (YEAR([Delivery Date]@row) + IF(MONTH([Delivery Date]@row)<= 5, -1, 0)))

• Options

Thanks very much. I will comback to you later.

Cheers,

Claude

• ✭✭✭✭✭✭
Options

Here are the results from when I plugged it in to make sure:

• Options

Thanks very much M. Newcome, the formula worked.

Cheers,

Claude

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

I've been seeing your name on so many of these questions and you always have great advice!

I've used your formula above which FINALLY worked (after so many trial and errors!) but my fiscal years are set up a bit differently. Generally new to formulas so hoping you can help me form one using the following dates

Thanks so much!

• ✭✭✭✭✭✭
Options

@Ashley Espinosa I would suggest an INDEX/MATCH with cross sheet references pulling from a reference table similar to what you have in your screenshot.

=INDEX({Reference Table FYQ Column}, MATCH(MAX(COLLECT({Reference Table Start Column}, {Reference Table Start Column}, @cell<= [Date Column]@row)), {Reference Table Start Column}, 0))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!