Formula to capture Quarter
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
Best Answer
-
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)))
Answers
-
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))
-
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
-
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)))
-
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
-
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)))
-
Thanks very much. I will comback to you later.
Cheers,
Claude
-
Here are the results from when I plugged it in to make sure:
-
Thanks very much M. Newcome, the formula worked.
Cheers,
Claude
-
Happy to help. 👍️
-
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!
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!