Number of quarters, last date of highest quarter and days after quarter

[Paparazzi]

Hello,

Have been registered for a while and have been using the forum to address my various problems over the years. Thanks a lot for all the help I have had and for the help provided to the various forumers.

I've now come up with a problem that I just cannot seem to either solve or a solution to which I cannot find addressed earlier.

Assuming I have 2 dates
Start Date: 15/07/2017
End Date: 22/10/2019

I would like to know:
a) How many FULL quarters are there between these two dates where
1st quarter ends on 14/10/2017
2nd quarter ends on 14/01/2018, etc.

b) The last date of the highest quarter. In this case, 14/07/2019

c) No. of dates from the end of the highest quarter 14/07/2019 to the End date 22/10/2019.

Would really appreciate any assistance.

Thank you

 

[WaterGypsy]

I'd consider framing the problem from the other perspective.
A list of Q end dates and then you might find it easier to work out .... for example COUNTIF range of dates is > start and < end date of period you consider ... etc

 

[Paparazzi]

Thought about it, I think I can use DATEDIF to solve the first part ... number of FULL quarters by using =int(DATEDIF(start_date,end_date,"M")/4). And, it does seem to be good enough.

Now, having got that, I'm not sure I'm racking up my brain sufficiently or not but cannot seem to think of a way get the 14/07/2019 nor of a way to find the number of days between 14/07/2019 & 22/10/2019. The key I guess is getting the 14/07/2019 as once that is obtained another DATEDIF will see me through to the remaining days.

WaterGypsy, I'm sorry I don't I can think of a way to use countif as I will primarily be having only two fields ... start_date & end_date.

I was wondering if I could somehow use the "number of quarters" obtained from my first result as in multiply that by 3 and get a date or something.

 

[WaterGypsy]

That is why I suggested you set up a list of the QTR end dates. It makes life a lot easier.. ... so put 14/4/17, 14/7/17, etc in a column on a separate sheet and name that range "QTR_ENDS".

Also name your start and end dates as FROM_DATE & TO_DATE respectively

To find the number of Quarters use: =COUNTIFS(QTR_ENDS,">="&FROM_DATE,QTR_ENDS,"<="&TO_DATE)-1

The latest QTR End date will be: =INDEX(QTR_ENDS,MATCH(TO_DATE,QTR_ENDS,1))

Name the result of that as LATEST_QTR

Then the number of days will be: =TO_DATE-LATEST_QTR

 

[steve the fish]

This may work for the complete quarters.

=FLOOR(DATEDIF(IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,15),DATE(YEAR(A1),MONTH(A1),15)),IF(DAY(A2)>13,DATE(YEAR(A2),MONTH(A2),15),DATE(YEAR(A2),MONTH(A2)-1,15)),"m")/3,1)