98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Get Quarters (Q1, Q2, Q3 AND Q4) from the given date
Get Quarters (Q1, Q2, Q3 AND Q4) from the given date
Resolved · Medium Priority · Version 365
Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
Get Quarters (Q1, Q2, Q3 AND Q4) from the given date
Hi,
I am currently looking for some pointers as to how to build a formula to get the quarters from the given date.
In cell A1, I have the following dates
01 Mar 2023
31 May 2022
15 Jul 2023
17 Aug 2023
03 Sep 2023
10 Dec 2023
Is there a formula that can help to get Quarters (Q1, Q2, Q3 and Q3) from the above dates?
Many Thanks
RE: Get Quarters (Q1, Q2, Q3 AND Q4) from the given date
Hi Kay,
Thank you for your question
The solution would be to write a nested IF function that applies 3 tests to the month number of each date and returns the appropriate Quarter number with the Q attached. The formula is the following:
="Q"&IF(MONTH(A1)>9,4,IF(MONTH(A1)>6,3,IF(MONTH(A1)>3,2,1)))
So the formula is testing first if any dates that are after September (equivalent to month number 9), then return Quarter number 4. Then the next test is if any dates that are after June (month number 6) return Quarter number 3. The next test is if any dates after March (month number 3),return Quarter number 2. For any other dates that don't meet these tests (eg. January, February and March), then return Quarter number 1
The & is used to 'join' or concatenate the relevant Quarter no. to the letter Q
I hope this makes sense
Kind regards
Martin Sutherland
MOS Applications Trainer
Sat 31 Dec 2022: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Adjusting the Elevation and Rotation in a 3-D ChartFor any 3-D chart you create, you can adjust the chart |