+ Reply to Thread
Results 1 to 8 of 8

Rounding dates UP or DOWN to nearest 15th or 30th of the month.

  1. #1
    Registered User
    Join Date
    07-29-2019
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    2

    Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    Hello. Given a specific day, round it up or down to the nearest half month, that is, either the 15th or 30th. Iím working with the following criteria:

    If >=1,<=8, represent as 30th of previous month
    If >=9,<=22, represent as 15th of current month
    If >=23,<=31, represent as 30th of current month.

    For example, 3 May is represented as 30 Apr, 9 May is represented as 15 May, 23 May is represented as 30 May.

    Iím only able to get half of it to work. Is the solution found with the IF function or should I be looking at EOMONTH or DATEDIF? This is what I got so far:

    =IF(AND(DAY(D2)>=1,DAY(D2)<=8),DATE(YEAR(D2),MONTH(D2),0),DATE(YEAR(D2),MONTH(D2)+0,15))

    And not sure how to handle leap year. Thanks.
    Attached Files Attached Files
    Last edited by swearjarmagnet; 02-11-2020 at 09:52 PM. Reason: Attachment

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,494

    Re: Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    Try this:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-29-2019
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    2

    Re: Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    OMG, it works perfectly. Thank you! So, it was nested IF statement in combination with EOMONTH. I see generally see how you used <=8 then <=22 to achieve days in the previous/current/current month supported by EOMONTH. It's hard to put into the correct excel language what I visualize. You have given me something to tear into, learn, and understand. Again, thank you.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,494

    Re: Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks!

  5. #5
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    421

    Re: Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    Late to the party, but here is a shorter formula for you to try:

    =IF(MOD(DAY(D2)-23,30)>15,EOMONTH(D2,-1)+15,MIN(EOMONTH(D2-8,0),EOMONTH(D2-8,-1)+30))

  6. #6
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    839

    Re: Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    Quote Originally Posted by Root_ View Post
    . . . =IF(MOD(DAY(D2)-23,30)>15,EOMONTH(D2,-1)+15,MIN(EOMONTH(D2-8,0),EOMONTH(D2-8,-1)+30))
    First, I believe you're off by 1 since 9th to 22nd should be 15th of same month, but your formula moves 9th back to 30th day of previous month and 23rd back to 15th of current month.

    If short is the goal, =EOMONTH(A1,-IF(DAY(A1)<9,2,1))+(1+(ABS(DAY(A1)-15.5)>7))*15

    No one's asked the OP how to handle February, which never has a 30th day, leap year or not.

  7. #7
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    421

    Re: Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    Quote Originally Posted by hrlngrv View Post
    First, I believe you're off by 1...
    Unfortunately, you are mistaken.

    Quote Originally Posted by hrlngrv View Post
    ... No one's asked the OP how to handle February...
    True. However, the OP has accepted Phuocam's formula that returns the last date in February. On the other hand, your formula returns 1st or 2nd of March for dates in the range of 23-Feb through 8-Mar -- which is nowhere near the OP requirements.

  8. #8
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    839

    Re: Rounding dates UP or DOWN to nearest 15th or 30th of the month.

    Quote Originally Posted by Root_ View Post
    Unfortunately, you are mistaken.
    Yes, I do seem to be. Sorry.

    As for handling February '30th' as either 29th or 28,

    =MIN(EOMONTH(D2,{0;-1}-(DAY(D2)<9))+(1+(ABS(DAY(D2)-15.5)>7))*{0;15})

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert all dates to first or 15th or whatever of month
    By carlito2002wgn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-14-2019, 10:40 AM
  2. Replies: 1
    Last Post: 04-29-2019, 10:04 PM
  3. Replies: 4
    Last Post: 04-02-2015, 03:19 AM
  4. Formula for Friday 14 days from start date, but closest to 15th or 30th
    By SiRCYRO in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-23-2013, 08:23 PM
  5. Rounding Dates up to the 15th or the last day of the month
    By karenbr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-12-2011, 04:52 PM
  6. Replies: 5
    Last Post: 07-09-2011, 03:51 AM
  7. Replies: 2
    Last Post: 09-16-2009, 06:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1