• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Start first quarter and dynamically based on selected date

Thomas Kuriakose

Active Member
Respected Sirs,

We need a formula to populate quarters dynamically on range of dates based on an input date for the next six quarters.

Kindly find attached a sample file with the input and requirement

For example if the input date is 09/15/2022 (B2) and the range of dates start with 10/18/2016 (A6) then this will be Q1 and go on until Q6.

Thank you very much for your support and guidance always,

with regards,
thomas
 

Attachments

p45cal

Well-Known Member
Do quarters start on set dates each year? For example January 1, April 1, July 1 and October 1.
Or do they start, as in your sheet, based on the 'last date' the 15th September 2022, then 15th December 2022 then March 15th 2023, then June 15th 2023 etc?
Or… something else?
 

Thomas Kuriakose

Active Member
Respected Sir,

Thank you very much for your support.

The quarters start on any given date. it is not set to first or fifteenth of the months, for example if the last date was 12th August (consider August end of month), and the quarter start date was 9th September then the first quarter would fall on September.

I hope this would help,

Thank you very much,

with regards,
thomas
 

Thomas Kuriakose

Active Member
Respected Sir,

The last date if 12th August(this is the input date from user B2 in the sheet), then Q1 should be from next month based on the date of 9th September.

So in this example case -
Dates
09/09/2022 - Q1
10/12/2022 - Q1
01/06/2023 - Q2

and following.

Yes the date of the first dat of
 

Thomas Kuriakose

Active Member
sorry, the above reply got clicked without completion

The date of the first Q1 can be considered as 09/01/2022.

Thank you very much once again,

The dates above mentioned are in format mm/dd/yyyy.

with regards,
thomas
 

p45cal

Well-Known Member
Test very carefully the following - it's probably wrong.
In B6:
="Q" & ROUNDUP((DATEDIF(EOMONTH(date,0),[@Date],"M")+1)/3,0)
or:
="Q" & ROUNDUP((DATEDIF(EOMONTH(date,0)+1,[@Date],"M")+1)/3,0)
they both give the same results with your sample data but your sample data doesn't really test; there are no dates either side of where the Quarter might change.
 

Thomas Kuriakose

Active Member
Respected Sir,

This worked perfectly for the sample data. Thank you so very much,

I will try this on the actual data and get back to you for any concern.

Highly appreciate your guidance and support,

with regards,
thomas
 

p45cal

Well-Known Member
I will try this on the actual data and get back to you for any concern.
You really need to test on made-up data with dates where you would expect the Quarters to change from one quarter to the next, and for 8 quarters to include dates before Q1 starts and for rollover to Q7. If the formula performs correctly you can start to rely on it.
But if that wasn't enough, I've used DATEDIF where here:
https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60399&ui=en-us&rs=en-us&ad=us
Microsoft warn: 'DATEDIF function may calculate incorrect results under certain scenarios. Please see the known issues section of this article for further details.'
 

Thomas Kuriakose

Active Member
Respected Sir,

The formula solution you provided worked perfectly on the actual data. Thank you so much for this great support.

We have now a similar case for the six quarters but now the change required is to start the quarter based on calendar quarter.

So if the selected date is in February and in March the first quarter should start from April
If the selected date is in January, the first quarter should start from January.

I clip the attached file with the example for your kind reference.

Thank you very much for this support,

with regards,
thomas
 

Attachments

p45cal

Well-Known Member
I can't work out from your sheet how you want the quarters to start and finish.
On Sheet2 in the top rows, you have August in column A and the expected result Q1 also starts in August.
In the lower rows you have Feb in column A but an expected Q1 starting in April!
 

Thomas Kuriakose

Active Member
Respected Sir,

My apologies i overlooked the month before saving the sheet2 - the selected month was supposed to be July - and hence the Q1 in this case should be starting from July.

The lower rows were examples for different selected months - in this case if the selected month is Feb, the Q1 should start from April.

If the month selected was in the middle of a quarter (Feb or Mar)- then the Q1 should start from April.
If the month selected was in the middle of a quarter (May or Jun)- then the Q1 should start from July.
If the month selected was in the middle of a quarter (Aug or Sep)- then the Q1 should start from October.
If the month selected was in the middle of a quarter (Nov or Dec)- then the Q1 should start from January.

If the month selected was Jan, then the Q1 should start from Jan
If the month selected was Apr, then the Q1 should start from Apr
If the month selected was Jul, then the Q1 should start from July
If the month selected was Oct, then the Q1 should start from Oct

Kindly find enclosed the updated file as well.

Thank you so much,

with regards,
thomas
 

Attachments

Thomas Kuriakose

Active Member
Respected Sir,

This is brilliant. Thank you very much for this solution. Yes, you are right, some of my expected results are not aligned with the data provided.

I will apply this in the actual data.

Really appreciate all the guidance and support,

with regards,
thomas
 
Top