get quarters q q

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » 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 - 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,

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

### RE: Get Quarters (Q1, Q2, Q3 AND Q4) from the given date

Hi Martin,

Thanks so much for helping as this is the correct solution to what I needed.

Many Thanks

Sat 31 Dec 2022: Automatically marked as resolved.

Training courses

Training information:

 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 What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Adjusting the Elevation and Rotation in a 3-D Chart

For any 3-D chart you create, you can adjust the chart

View all Excel hints and tips