# How to calculate Q1, Q2… but for a given date and not 01 Jan

I have been trying to calculate the Quarter period but not for a year that starts on 01 Jan but for a date that denotes the end of year which could be 03-22 for example.

There are a lot of examples on how to do Q1, Q2 but none I can find or can develop which I have been trying to do for days now.

The input will be:

1. Current Date
2. Frequency (months)

3. Tax end (Month & Day)

For the inputs

1. Current Date > 2018-02-01
2. Frequency (months) 3

3. Tax end (Month & Day) 12-31

Result

Current quarter start=2018-01-01 end =2018-03-31

Last Quarter start=2017-01-10 end =2017-31-12

The closest I can get is Google Spread Sheet which can be copied but it breaks down when it stretches over years.

The closest I have got is:

=DATE(YEAR(A5),FLOOR(MONTH(A5)-MONTH(G5),E5)+1,DAY(G5))

Where A5=current date G5=period in months. E5=Tax Filing date (I.e end period) + 1 would give you start period.

I just can't figure it out or even if it is possible in a formula.

• Your attached sample file link is not public.. can't xs the file. error : "Need to request permission".. – p._phidot_ Aug 17 '18 at 14:05
• I've re-read the question 4 times now and still not sure what you're asking :) Do you want quarter start dates from any given date? If so use DateAdd. – Absinthe Aug 17 '18 at 22:08

Perhaps this is what you want:

Here are the formulas:

\$C\$4:     =EDATE(DATE(YEAR([@[Current Date]]),MONTH(EndOfYear),DAY(EndOfYear)+1),CHOOSE(MATCH([@[Current Date]],EDATE(EndOfYear,{-12,-9,-6,-3,0,3,6,9,12})),-12,-9,-6,-3,0,3,6,9,12))
\$D\$4:     =EDATE([@[Current Quarter start]]-1,3)
\$E\$4:     =EDATE([@[Current Quarter start]],-3)
\$F\$4:     =[@[Current Quarter start]]-1

EndOfYear is a normal date. In the formula in C4, we normalize it to refer to a year relative to Current Date, thereby getting around the multiyear issue.

There is an assumption that EndOfYear will not be on 29-Feb, otherwise the results may be unexpected.

Note that in your example, your date formats are inconsistent. I have formatted the dates in my example with the months spelled out so as to be unambiguous.

Since you are looking for Quarters, the Frequency (months) column is ignored.