Counting Calendar Quarters

[ASPENCO]

I would like to be able to calculate the number of calendar quarters that have passed since a specific date. In another words, if the date were 11/20/14, the correct answer would be 2 (12/31/14 and 3/31/15). If the date were 11/20/13 then the answer would be 6. Thank you for your assistance.

[humdingaling]

i suppose you mean from today

Please Login or Register  to view this content.

A1= date
B1= today

[ASPENCO]

Thank you. Much appreciated.

[samba_ravi]

Some cases @ humdingaling formula will give wrong answer
if the two dates are 15-Oct-14 31-05-15 it the result will be 3 but actual result is 2

[newdoverman]

I haven't gone through all the date combinations but these seem to work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jhren]

I haven't gone through all the date combinations but these seem to work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Try 3/31/2015 to 4/1/2015.

Both result in 0.

According to OP, it could/should be 1.

Additionally, neither formula is based on actual quarter end dates (i.e. 3/31, 6/30, 9/30, 12/31).

This one seems to work the way OP'er wants...

Please Login or Register  to view this content.

Needs modified if OP'er actually wants full quarters elapsed.

[newdoverman]

So, you are trying to say that 1 day is a quarter?....don't think so.

[jhren]

So, you are trying to say that 1 day is a quarter?....don't think so.

Of course not. But, for example, from 3/31 to 4/1 of any year, the first quarter passed. OP's example show calendar quarter end dates as the "passed" criteria.

[samba_ravi]

Try
=INT(DATEDIF(EOMONTH(A3,INT(MONTH(A3)/3)*3-MONTH(A3)),B3,"m")/3)
or
=INT(DATEDIF(EOMONTH(A3,INT(MONTH(A3)/3)*3-MONTH(A3)),B3+1,"m")/3)

[ASPENCO]

Actually, it needed a small adjustment to add "1" quarter for those months that were either 3,6,9, or 12. Thanks again.

[samba_ravi]

=int((datedif(eomonth(a3,-mod(month(a3)-1,3)-1),eomonth(b3,0),"m")-1)/3)+1
try this