Rounding Dates to Next Quarterly Period

Hi all,

I have a number of rent dates and need to round them up to the next quarter. However, I would like to be able to alter the start date so that the dates are rounded to the next 3 month quarterly period (not necessarily the same as the proper Quarter dates)

So, I am looking for a fomula to put in C1 that rounds the date in B1 (by way of example "05/02/15") up to the next quarter date. But I do not mean quarter as in March, June, Sept, Dec. Instead, if the start date is "31/1/15" (in Cell A1) then the next quarters would become 30/04/15, 30/07/15, 30/10/15. Therefore the formular should return "30/04/15" in cell C1.

Does anyone have a formula?

Thanks

Try:

=INDEX(INDEX(EOMONTH(A1,{3,6,9,12}),0),MATCH(TRUE,INDEX(EOMONTH(A1,{3,6,9,12})>B1,0),0))

Thanks - seems to work within 4 quarters, but if the start is 31/01/15 (A1) and the date to round is 01/02/2017 (B1) the formula does not seem to work. (it should return "30/04/2017"

Appreciate your help

Try:

=INDEX(INDEX(EOMONTH(DATE(YEAR(B1),MONTH(A1),DAY(A1)),{3,6,9,12}),0),MATCH(TRUE,INDEX(EOMONTH(DATE(YEAR(B1),MONTH(A1),DAY(A1)),{3,6,9,12})>B1,0),0))

That's brilliant thanks. Any idea how the formular would need to change if I wanted it to round to the nearest quater date (as opposed to rounding up as above)??

See if this works:

=INDEX(INDEX(EOMONTH(DATE(YEAR(B1),MONTH(A1),DAY(A1)),{3,6,9,12}),0),MATCH(MIN(INDEX(ABS(INDEX(EOMONTH(DATE(YEAR(B1),MONTH(A1),DAY(A1)),{3,6,9,12})-B1,0)),0)),INDEX(ABS(INDEX(EOMONTH(DATE(YEAR(B1),MONTH(A1),DAY(A1)),{3,6,9,12})-B1,0)),0)))

that doesn't seem to work unfortunately


By way of further example, if the start date is set (A1) at 31/01/15, the quarter dates become 30/04/15, 31/07/15, 30/10/15, 31/01/16, 30/04/16 etc.

e.g If the date to round (B1) is 01/05/16 I am looking for a formula that rounds this date to its nearest quarter date - i.e 30/04/16?

Thanks very much

Hi LG2008,

I have tried your sample dates and my formula yields exactly that result.

Can you try it again and make sure your dates are Jan 31, 2015 in A1 and May 1, 2016 in B1.