How to get date (last day of prev. quarter) from quarter?

[#1]

Hi,

I need to compare results of current quarter and previous quarter (I use set analysis) and I need to extract whole date from quarter..

I know how to get max month from previous quarter -> previous_quarter_month =Month(QuarterEnd(maxdate))-3

(maxdate is variable max(date))

but I do not know how to extract also the last day of the month..because if I use day_previous_quarter =day(MonthEnd(previous_quarter_month)) it returns bad value (e.g. 31 even if the month is June which has just 30 days..)

In set analysis I need to define month and day separately.

Any idea how to find out the whole date of the last day of the previous quarter?

Thank you so much.

Nela

[stigchel · Accepted solution]

The problem is with the function month, this function does not return a date, but only the month. MonthEnd however expects a date.

So for day_previous_quarter try use

=day(QuarterEnd('$(=Addmonths(Max(Date),-3))'))

Also mind the quotes

[swuehl]

You can use a second argument to function quarterend() to indicate previous quarter:

=QuarterEnd(today(),-1)

Or

=QuarterEnd(Max(Date), -1)

This will return a timestamp, use floor if you want to get the integer:

=Date(Floor(QuarterEnd(max(Date),-1)) )

Or to get the Month or Day:

=Month(QuarterEnd(max(Date),-1))

=Day(QuarterEnd(max(Date), -1))

[MarcoWedel]

DayName(QuarterEnd(maxdate),-1)

[#5]

Thank you so much, it works