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

Try this:

Please Login or Register  to view this content.

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.

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!

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))

Originally Posted by Root_

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

Originally Posted by hrlngrv

First, I believe you're off by 1...

Unfortunately, you are mistaken.

Originally Posted by hrlngrv

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

Originally Posted by Root_

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})