I am trying to determine the 3 months in a quarter based on a Date

[sjahub]

Month 1
Month 2
Month 3

Example Date 7/31/2016

results would be

Month 1 6
Month 2 7
Month 3 8

 

[Shteven]

If your date is in cell A1:

Month 1: =IF(MONTH(A1)-1=0,12,MONTH(A1)-1)
Month 2: =MONTH(A1)
Month 3: =IF(MONTH(A1)+1=13,1,MONTH(A1)+1)

 

[Im_Offset]

I don't think that formula works for finding the start of the quarter.

How about this (Assume date is in cell A1, and Month 1, 2, & 3 are in cells B3, B4, & B5. See attached spreadsheet):

Cell B3 (Month 1) =IF(MONTH(A1)<4,1,IF(MONTH(A1)<7,4,IF(MONTH(A1)<10,7,10)))
Cell B4 (Month 2) = B3 + 1
Cell B5 (Month 3) = B4 + 1

 

[Chihiro]

Not exactly sure on what's needed. But I'd recommend setting up named ranges to serve as lookup table.

See attached.

 

[John Jairo V]

Hi to all!

If you have date in [A1], put in [C1]:
=MONTH(EDATE(A$1,ROWS(C$1:C1)-2))

And drag it down. Blessings!

 

[Chihiro]

Neat formula.

But I took Op's question to be like something below.
Q1 - Dec, Jan, Feb
Q2 - Mar, Apr, May
Q3 - Jun, Jul, Aug
Q4 - Sep, Oct, Nov

So if Date is between 6/1/2016 to 8/31/2016 then
Month 1 = 6
Month 2 = 7
Month 3 = 8

If date between 12/1/2015 to 2/29/2016 then
Month 1 = 12
Month 2 = 1
Month 3 = 2

Op can you clarify your requirement a bit? If your question isn't answered by one of the post above.

 

[Im_Offset]

Neat formula.

But I took Op's question to be like something below.
Q1 - Dec, Jan, Feb
Q2 - Mar, Apr, May
Q3 - Jun, Jul, Aug
Q4 - Sep, Oct, Nov

So if Date is between 6/1/2016 to 8/31/2016 then
Month 1 = 6
Month 2 = 7
Month 3 = 8

If date between 12/1/2015 to 2/29/2016 then
Month 1 = 12
Month 2 = 1
Month 3 = 2

Op can you clarify your requirement a bit? If your question isn't answered by one of the post above.

Right! I did almost the same thing but in the USA Quarter 1 is Jan, Feb, & Mar. Q2 is Apr, May, & June. etc.
The formula I used basically says if the current month is less than month 4, the Quarter is 1 and the months in the quarter are Jan, Feb & Mar (1, 2, & 3 respectively). If the current month is less than 7, then it is Q2 and the months are 4, 5, & 6 (Apr, May, Jun).....and so on. That seems to answer the mail for what he was asking.