how to generate quarters with formulas in Excel

Please see attached file.

i have the cells with green background that are my start dates and end dates.  the cells highlighted in yellow are the quarters which i generate manually.  can there be a formula that when i copy to other cells, it generates those quarters for me, instead of all this manual work.  there has to be a better way with formula, but i could not find anything in google.

any help is appreciated.
EE.xlsx
LVL 6
FloraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
your values highlighted seems contradict with each others.

for example:
row 2 is not generating values in quarters.

The value of C3 = "01/04/15- 30/06/15", while value in D3 = "01/07/16- 30/09/16".
Are you putting a wrong year in D3?

SnapShot.png
if I understand your requirement, you can try this formula, which link to a module function.

Function getQuarter(ByVal StartDate As Date, ByVal EndDate As Date, ByVal Counter As Integer) As String
    On Error GoTo Err
    Dim dateFormat As String, separator As String
    Dim sPart As String, ePart As String
    Dim tStartDate As Date, tEndDate As Date
    Dim internalCounter As Integer
    
    dateFormat = "dd/MM/yy"
    separator = "- "
    If Counter = 1 Then
        sPart = Format(StartDate, dateFormat)
        tStartDate = StartDate
    Else
        tStartDate = Application.WorksheetFunction.EoMonth(StartDate, -1) + 1
    End If
    tEndDate = Application.WorksheetFunction.EoMonth(StartDate, -1)
    internalCounter = 0
    Do While DateAdd("M", 3, tEndDate) - 1 < EndDate And internalCounter + 1 <= Counter
        If Counter <> 1 Then
            sPart = Format(Application.WorksheetFunction.EoMonth(tStartDate, -1) + 1, dateFormat)
        End If
        tStartDate = DateAdd("M", 3, tStartDate)
        tEndDate = DateAdd("M", 3, tEndDate)
        internalCounter = internalCounter + 1
    Loop
    ePart = Format(tEndDate, dateFormat)
    If Counter > internalCounter Then
        getQuarter = ""
    Else
        getQuarter = sPart & separator & ePart
    End If
    Exit Function
Err:
    getQuarter = ""
End Function

Open in new window


so you will need to try formula such as:

=getQuarter($A2,$B2,1)

Open in new window


to get the first quarter.

To get 2nd quarter, use:

=getQuarter($A2,$B2,2)

Open in new window

and so on.
EE_b.xlsm
0
AlanConsultantCommented:
Hi Flora,

I think the attached does what you want, and no VBA / custom functions required ;-)


Alan.
EE-29068235-Version1.xlsx
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
You can use the EOMONTH function for this.

Formula in column C
=TEXT($A2,"dd/mm/yyyy")&" - "&TEXT(EOMONTH($A2,2),"dd/mm/yyyy")

Formula in column D
=TEXT(EOMONTH($A2,3),"dd/mm/yyyy")&" - "&TEXT(EOMONTH($A2,5),"dd/mm/yyyy")
The numbers that are highlighted in bold will need adjusting as you copy the formula across:
3 & 5
6 & 8
9 & 11
etc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FloraAuthor Commented:
Ryan,

many thanks.  yes i am sorry. that was a typo.
0
FloraAuthor Commented:
thank you very much everyone.


Alan, you nailed it with your formula. it worked.
2017-11-14-15_39_05-EE.xlsx---Excel.png
0
FloraAuthor Commented:
Thanks Everyone
0
FloraAuthor Commented:
Hi Alan,

i faced problem with your formula on the computer with american date format MM/DD/YY

i posted a new question for this. link is https://www.experts-exchange.com/questions/29072413/Date-formula-do-not-work-in-Machines-with-american-date-format.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.