Any other way to do quarter flag?

[doctortt]

Hi, I'm sure there is another way to do this. Can a guru shed some light?

M2: June 30, 2013
M3: July 31, 2013

N2: July 31, 2013
N3: August 31, 2013

O2: August 31, 2013
O3: September 30, 2013

Assumptions!$E$8: 3 (in Months)

I get a flag of 1 using the codes below because on September 30, 2013, it's a quarter.

[VBA]=1*OR(AND(NOT(MOD(MONTH(O3),3)=0),O11=Assumptions!$E$8),AND(MOD(MONTH(O3),3 )=0,NOT(O11=Assumptions!$E$8)))[/VBA]

[werafa]

It seems that you are wanting to ID the quarter for a date.
Why don't you use the MONTH function to return the month number, then divide by 4 to return the Quarter number?

[doctortt]

Hi, thanks for your reply. Would you mind elaborate a bit? Why divide by 4?

[werafa]

The month function Month(mydate) should return a number between 1 and 12 (ie, month 1 to 12).

so:
myQ = month(mydate)
myQ = myQ + 4 ' to make the maths work
myQ = myQ/4

'Feb = 2, so (2+4)/4 = 1.5

truncate to get 1 (for Q1)

Use this logic to build your formula. (this is written in VBA style, but the logic and functions are the same)