Calculate the Last Day of the Prior Quarter

Select DateAdd(day, -1, dateadd(qq, DateDiff(qq, 0, @Date), 0)) 

Select dateadd(qq, DateDiff(qq, 0, @Date), -1) 

SELECT current_date today,
    date_trunc('quarter', today) as cur_qrt,
    dateadd('day', -1, cur_qrt) as last_day_of_prior_qrt,
    dateadd('day', -1, dateadd('quarter', 1, cur_qrt)) as last_day_of_current_qrt;

TODAY	CUR_QRT	LAST_DAY_OF_PRIOR_QRT	LAST_DAY_OF_CURRENT_QRT
2022-01-19	2022-01-01	2021-12-31	2022-03-31

Get last day of previous quarter

This should work for VBA. It just determines the first day of the current quarter and then subtracts one day.

Debug.Print DateAdd("q", DatePart("q", Date) - 1, "1/1/" & Year(Date)) - 1

End of previous quarter / last day of previous quarter

Here are some possibilities with functions from packages zoo and timeDate, and base R. The zoo code was improved by @G.Grothendieck, and he also suggested the base alternative (thanks a lot!). I leave the lubridate solution(s) to someone else.

First, use class yearqtr in package zoo to represent the quarterly data. You may then use as.Date.yearqtr and the frac argument "which is a number between 0 and 1 inclusive that indicates the fraction of the way through the period that the result represents. The default is 0 which means the beginning of the period" (see ?yearqtr, and ?yearmon for frac).

Step by step:

library(zoo)

date <- as.Date("2014-11-03")

# current quarter
current_q <- as.yearqtr(date)
current_q
# [1] "2014 Q4"

# first date in current quarter
first_date_current_q <- as.Date(current_q, frac = 0)
first_date_current_q 
# [1] "2014-10-01"

# last date in previous quarter
last_date_prev_q <- first_date_current_q - 1
last_date_prev_q
# [1] "2014-09-30"

And a short version by @G.Grothendieck (thanks!)

as.Date(as.yearqtr(date)) - 1
# [1] "2014-09-30"

A nice base R solution by @G.Grothendieck

as.Date(cut(date, "quarter")) - 1
# [1] "2014-09-30"

Another possibility is to use timeFirstDayInQuarter and timeLastDayInQuarter functions in package timeDate:

library(timeDate)
timeLastDayInQuarter(timeFirstDayInQuarter(date) - 1)
# GMT
# [1] [2014-09-30]

excel formula for previous quarters

To return the quarter | year corresponding to 3 months ago, i.e. :

"previous quarter & year"

simply replace col H date references with edate(date,-3). Cell J17 formula to enter is then:

="Q"&ROUNDUP(MONTH(EDATE(H17,-3))/3,0)&" "&YEAR(EDATE(H17,-3))

If you have Office 365 compatible version of Excel, you could shorten (albeit ever so slightly in this case) with the let function as so:

=LET(x,EDATE(H17,-3),"Q"&ROUNDUP(MONTH(x)/3,0)&" "&YEAR(x))

How to get previous -previous quarters month and year in SQL Server

If it is the last day of the quarter then here is one solution. This should work going forwards and backwards.

DECLARE @ReportDate DATETIME = '2020-07-14'
DECLARE @QuarterInterval INT = -2

DECLARE @EndOfQuarter DATETIME =(SELECT DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0,DATEADD(QUARTER,@QuarterInterval , @ReportDate)) +1, 0)))

SELECT 
    TheDate = @EndOfQuarter,
    TheMonth = DATEPART(MONTH,@EndOfQuarter),
    TheYear = DATEPART(YEAR,@EndOfQuarter)

Find next quarter end date given previous quarter end date using Java

To answer your question, I think you are looking for this :

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yy");
LocalDate end = LocalDate.parse("30-09-20", formatter)
    .plusMonths(3)                             // add three months to your date
    .with(TemporalAdjusters.lastDayOfMonth()); // with the last day of the month

Note: don't use the legacy Date library, you tagged your question Java-8 which mean you can use java-time API.

---

Get last day of current quarter

@deHaar have reason, to get the end date of curent quarter, I would suggest to use :

public LocalDate lastDayFromDateQuarter(String date) {
    final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yy");
    LocalDate ld = LocalDate.parse(date, formatter);
    int quarter = ld.get(IsoFields.QUARTER_OF_YEAR); // Get the Quarter, 1, 2, 3, 4
    // Then create a new date with new quarter * 3 and last day of month
    return ld.withMonth(quarter * 3).with(TemporalAdjusters.lastDayOfMonth());
}

Get last day of next quarter

To get the last day of the next quarter, then you just can add three months to your date like so :

public static LocalDate lastDayFromDateQuarter(String date) {
    final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yy");
    LocalDate ld = LocalDate.parse(date, formatter);
    int quarter = ld.get(IsoFields.QUARTER_OF_YEAR);
    return ld.withMonth(quarter * 3)
            .plusMonths(3)
            .with(TemporalAdjusters.lastDayOfMonth());
}

---

Related Topics