Rounding Dates to Nearest Quarterly Period

[LG2008]

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 nearest 3 month period (not necessarily the same as the proper Quarter dates)

e.g if the Start date is 31/01/2015 in Cell A1, I want a date of 05/02/2015 in cell B1 to be returned as 30/04/2015 in cell C1

similarly, if date in B1 is 17/08/2015, C1 shoiuld return 31/10/2015

The idea is that the cell in A1 can be flexible

Does anyone have a forumla?

Many thanks

 

[LG2008]

That's brilliant, thanks!

any idea how it would change if I wanted it to round to the nearest quarter (as opposed to rounding up to the next quarter?)

 

[StephenCrump]

any idea how it would change if I wanted it to round to the nearest quarter (as opposed to rounding up to the next quarter?)

Can you please clarify with some examples what you mean by nearest quarter?

 

[LG2008]

So, if the start date is set at 31/01/15, the quarter dates become 30/04/15, 31/07/15 etc.

e.g If the date to round is 01/05/15, the previous formula you gave me would round the date up to 31/07/15.

Can the formula (which is very neat) be adapted to round the date to the nearest quarter (i.e round up or down instead of always rounding up) - i.e 30/04/15?

Thanks very much

 

[StephenCrump]

Try:

=EOMONTH(A1,MOD(MONTH(StartDate)-MONTH(A1),3)-3*(0>SUM(A1-EOMONTH(A1,MOD(MONTH(StartDate)-MONTH(A1),3)-{0,3}))))

This will round up to the next quarter if the date is equidistant, e.g. if StartDate is in May so that the quarter end dates are 31 May, 31 Aug etc, then 16 July is 46 days from 31 May and also 46 days from 31 August. My formula returns 31 August.

To round down to 31 May, simply change to .. 0>=SUM ..