Results 1 to 8 of 8

Thread: Rounding Dates to Next Quarterly Period

  1. #1

    Rounding Dates to Next Quarterly Period



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Try:

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


  3. #3
    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

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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))


  5. #5
    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)??

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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)))


  7. #7
    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

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •