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

Paparazzi

New Member
Joined
May 26, 2003
Messages
21
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
:)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top