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

 

[oldbrewer]

it is not clear what the rules are

start date 31/1/2015 how do you get 5/2/2015

anyway a lookup table would seem the way to go

 

[LG2008]

Ok 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. 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. Is this any clearer?

 

[oldbrewer]

SO START DATE OF 25/1/15 the rule is go to end of month and add 3 more months ?

[TABLE="width: 683"]
<colgroup><col><col><col><col span="3"><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2015[/TD]
[TD="align: right"]30/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]date[/TD]
[TD]quarter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/02/2015[/TD]
[TD="align: right"]31/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]12/01/2015[/TD]
[TD="align: right"]30/04/2015[/TD]
[TD]#######[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/03/2015[/TD]
[TD="align: right"]30/06/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]23/02/2015[/TD]
[TD="align: right"]31/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/04/2015[/TD]
[TD="align: right"]31/07/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]02/05/2015[/TD]
[TD="align: right"]31/08/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/05/2015[/TD]
[TD="align: right"]31/08/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/06/2015[/TD]
[TD="align: right"]30/09/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/07/2015[/TD]
[TD="align: right"]31/10/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2015[/TD]
[TD="align: right"]30/11/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/09/2015[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/10/2015[/TD]
[TD="align: right"]31/01/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/11/2015[/TD]
[TD="align: right"]29/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/12/2015[/TD]
[TD="align: right"]31/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]##########[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=VLOOKUP(B4,mytable,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[LG2008]

Not quite.

From whenever the start date is, the quarterly profile will be, as you say, the end of the month plus three months. eg start date of 31/1/15 (e.g in cell A1) will mean quarter dates of 30/04/15, 31/07/15, 31/10/15 and 31/01/16.

I then have a date (e.g in Cell B1) on which rent is payabale (e.g 02/04/15) and I want to know what the quarter end is (i.e formula should give me "30/04/15" because this is the end of next quarter as in the blue colours above)

However, if the start date is changed to 28/02/15, the quarter dates change to 31/05/14, 30/08/15, 30/11/15, 28/02/16 and the formula should give me "31/05/15"

 

[oldbrewer]

sort out your 14's 15's and 16's

 

[LG2008]

Not quite.

From whenever the start date is, the quarterly profile will be, as you say, the end of the month plus three months. eg start date of 31/1/15 (e.g in cell A1) will mean quarter dates of 30/04/15, 31/07/15, 31/10/15 and 31/01/16.

I then have a date (e.g in Cell B1) on which rent is payabale (e.g 02/04/15) and I want to know what the quarter end is (i.e formula should give me "30/04/15" because this is the end of next quarter as in the blue colours above)

However, if the start date is changed to 28/02/15, the quarter dates change to 31/05/15, 30/08/15, 30/11/15, 28/02/16 and the formula should give me"31/05/15"

Example 1:
Date Start in A1: 31/01/15
Date of Rent in B1: 01/02/15
Date Required in C1 30/04/15

Example 2:
Date Start in A1: 31/01/15
Date of Rent in B1: 01/02/17
Date Required in C1: 30/04/17

Example 1:
Date Start in A1: 28/02/15
Date of Rent in B1: 01/03/17
Date Required in C1: 31/05/17

 

[oldbrewer]

the quarter dates change to 31/05/14, 30/08/15,

that is what i meant

 

[shg]

Maybe ...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]

31-Jan-15​

[/td][td]B1: Input[/td][/tr]
[tr][td]

2​

[/td][td]

Date​

[/td][td]

End of Qtr​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

31-Jan-15​

[/td][td]

31-Jan-15​

[/td][td]B3: =EOMONTH($B$1, CEILING(DATEDIF($B$1 - DAY($B$1) + 1, A3, "m"), 3))[/td][/tr]
[tr][td]

4​

[/td][td]

01-Feb-15​

[/td][td]

30-Apr-15​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

20-Mar-15​

[/td][td]

30-Apr-15​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

24-Mar-15​

[/td][td]

30-Apr-15​

[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

30-Apr-15​

[/td][td]

30-Apr-15​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

09-May-15​

[/td][td]

31-Jul-15​

[/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

02-Jun-15​

[/td][td]

31-Jul-15​

[/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

26-Jul-15​

[/td][td]

31-Jul-15​

[/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

11-Aug-15​

[/td][td]

31-Oct-15​

[/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

27-Aug-15​

[/td][td]

31-Oct-15​

[/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

08-Oct-15​

[/td][td]

31-Oct-15​

[/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

05-Dec-15​

[/td][td]

31-Jan-16​

[/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

30-Jan-16​

[/td][td]

31-Jan-16​

[/td][td][/td][/tr]
[/table]

 

[StephenCrump]

Or maybe:

=EOMONTH(A1,MOD(MONTH(StartDate)-MONTH(A1),3))