roundup a month

[JR]

If a cell returns a date between the 2nd and last day of a given month. What
worksheet function can be used to return the 1st day of the following month?

 

[Sheeloo]

If the function is in A1 then use this in B1
=DATE(YEAR(A1),MONTH(A1)+1,1)

or you can replace A1 with the formula you have returning a date between 2nd
and last day of the month...

 

[Rick Rothstein]

Assuming your cell is A1...

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>1),1)

 

[JR]

Thank you for responding. This works great!

 

[JR]

Thanks for your answer. In comparing the two responses you have added
+(DAY(A1)>1),1). I was just wondering why the difference?

 

[Rick Rothstein]

Unless I read your question incorrectly, you seemed to say you wanted the
formula to return the first of the next month ONLY for day 2 through the end
of the month which I took to mean if the date was the first of the month,
the date shouldn't change. The formula I posted does that. The formula
Sheeloo posted will advance the first of the month to the month as well as
from day 2 on unless you house it in an IF function call that looks
specifically for that condition... the "added" part of my formula takes care
of that test automatically, so my formula can be used exactly as posted
(again, assuming I understood your question correctly).

 

[JR]

Yes, you understood the question and you are correct in adding the
(DAY(A1)>1),1) otherwise is the first formula changes the month not
considering if it is the 1st or 2nd.
thank you