Quarters between dates

[Excel_Loser]

Hi everyone - first of all thanks to everyone who will help me on this, I have to tried to do it by myself but it’s been a constant struggle.

I need to calculate the number of quarters between 2 dates but it has to include both quarters of start and end date for example...

Start: 6/30/18
End: 1/1/2019

Quarters between = 4
It will need to count the quarters that each date is sitting in

Also, I need help calculating number of quarters between start and end of year for that specific date, I figure this will be easy once I have my first problem fixed

Thank you all!

[mattmc419]

I think this would do it:

Please Login or Register  to view this content.

[Excel_Loser]

This doesn't work because if you change start date to 7/1/2018 - the result you get is 4 when it should be 3

[truk2]

How's this? Substitute your cell references for "Date1" and "Date2"

Please Login or Register  to view this content.

[Phuocam]

Or try:

=DATEDIF(DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+1,1),DATE(YEAR(B1),CEILING(MONTH(B1)+1,3)+1,1),"m")/3

[FlameRetired]

Another way.

Please Login or Register  to view this content.

	A	B	C	D	E
1	6/30/2018	4	In Name Manager	D_per_Yr	=(400*365.25-3)/400
2	1/1/2019		In Name Manager	D_per_Mo	=D_per_Yr/12
3			In B1 =MATCH(A2,EOMONTH(A1,INDEX((ROW(INDIRECT("1:"&INT((A2-A1+1)/D_per_Mo)))-2)*3,0)),1)
4

[FlameRetired]

Edit Ignore this one. It doesn't work.

Another way.

Please Login or Register  to view this content.

[truk2]

Considering that several methods have been suggested, what factors do people think makes a 'better' solution for a context like this?

I'm inclined to suggest that simpler solutions with less nested brackets are less error prone to input errors, and fewer or easier to understand functions are better. Particularly for someone who needed the help.
But do some of the harder to read ones offer performance benefits? Or is just a case of people suggesting the functions that they are most familiar with?

[FlameRetired]

This sounds like a question better suite for the 'Water Cooler' sub-forum.

Consider starting a thread there ... we are at risk of "hijacking" the OP's thread here.

[FlameRetired]

Another. Array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.