0

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
1

Perhaps this is what you want:

enter image description here

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.