How to round dates up?

[imdt3000]

I need to round all dates that are between the 2nd and 15th (both #'s included) up to the 15tht and all dates that are between the 16th to the 31st (both #'s included) to the 1st of the next month.
Thanks

 

[Dannyh1]

Try:

=IF(AND(DAY(A1)>=2,DAY(A1)<=15),VALUE(15&"/"&MONTH(A1)&"/"&YEAR(A1)),VALUE(1&"/"&MONTH(A1)+1&"/"&YEAR(A1)))

 

[Jonmo1]

Perhaps

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>15),LOOKUP(DAY(A1),{1,2,16},{1,15,1}))

 

[imdt3000]

Thanks Danny but doesn't give me a correct answer. For example in i put 12/27/10 i got 1/13/10. It should be 1/1/11.
I was trying =IF(AND(DAY(B9)>1,DAY(B9)<=15),DATE(YEAR(B9),MONTH(B9),15),DATE(YEAR(B9),MONTH(B9)+1,1)) but only get the date to the 1st but can't get the 15th.

 

[barry houdini]

So if B9 is 1st or 15th the formula should return the same date? Try this

=IF(DAY(B9-1)<15,B9-DAY(B9)+15,B9+17-DAY(B9+16))

 

[imdt3000]

Thanks !!!!