Formula to capture Quarter

claude.ngongang80316

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. 

 

Thanks in advance for your help and assistance.

 

Cheers,

 

Claude

Paul Newcome

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

Paul Newcome

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

claude.ngongang80316

Good Day M. Newcome,

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

Thanks in advance,

Claude

Paul Newcome

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

claude.ngongang80316

Results.xlsx

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

Thanks in advance,

Claude

Paul Newcome

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

claude.ngongang80316

Thanks very much. I will comback to you later.

Cheers,

Claude

Paul Newcome

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

claude.ngongang80316

Thanks very much M. Newcome, the formula worked.

Cheers,

Claude

Paul Newcome

Happy to help. 👍️

Ashley Espinosa

Hi @Paul Newcome

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!

Paul Newcome

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