Caclulate month of quarter (1st month - 3rd month)

[kuraitori]

Hello,

Have calendar quarter (1-4) in one column, have month serial value in the other (1-12). How do I caculate with month of the quarter that the date is?

so for
december

month serial = 12
quarter = 4
month of quarter = 3

because december is the 3rd month of that Quarter.

I would like a formula that can determine that.

Any ideas?

[martindwilson]

=int((month(a1)-1)/3)+1 gives 1/4 of calendar year
you could use
=CHOOSE(MONTH(A1),1,2,3,1,2,3,1,2,3,1,2,3) to return which month of 1/4
similarly you could do same for 1/4
=CHOOSE(MONTH(E1),1,1,1,2,2,2,3,3,3,4,4,4)

[daddylonglegs]

For month of quarter try

=MOD(MONTH(A1)-1,3)+1

where A1 is a date

[kuraitori]

Martin: that is the formula I used to calculate the calendar quarter, thank you

Daddylonglegs: Formula works like a charm, thank you

[shgupta278]

How can I get actual month number if I have month of quarter and quarter number ?

Like I have 2nd month of Qtr and Qtr =3, so actual month = 11

[protonLeah]

shgupta278,
When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)

---

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.