Day of month is 20th or later then output 1st day of next month

[billybbeenn8]

I have a list of dates in XX/XX/XXXX format and I am looking for a formula to output a new date as the first day of the NEXT month if the day of the current month is on the 20th or later. Then if the day of the month is before the 20th to output a date that is on the first of the current month. Any help is greatly appreciated.


3/16/2015 should be 3/1/2015
5/20/2012 should be 6/1/2012

[Rick Rothstein]

Here is one way to do it...

=IF(DAY(A2)<20,A2-DAY(A2)+1,A2+13-DAY(A2+12))

[Rick Rothstein]

And here is a shorter formula that will also work...

=EOMONTH(A1-DAY(A1),0+(DAY(A1)>19))+1

[billybbeenn8]

Thanks so much!

[Phuocam]

A little shorter:

=EOMONTH(A1,-(DAY(A1)<20))+1