If date 15th or less, show beginning of month, if 16th or greater, show beginning of next month

[bobletcs123]

Hi There,

Working on warranties for products and need to determine if it should start the beginning of that month, or beginning of next month.

We consider the 1st-15th would be that month, 16th to EOM would be Next month:
So...
8/17/18 = 9/1/18
8/14/18 = 8/1/18
8/7/18 = 8/1/18
9/7/18 = 9/1/18 etc

What would be the best way to go about a formula for this?
Thx

 

[Gerald Higgins]

Hi, welcome to the board.

With your date in A1, perhaps something like this . . .

=DATE(YEAR(A1),MONTH(A1),IF(DAY(A1)<16,1,DAY(EOMONTH(A1,0))))

 

[bobletcs123]

Thanks so much Gerald, this works great for getting the beginning of month to populate, but for the dates 16th or greater, it is showing the EOM of that current month, but I need it to show the beginning of next... somehow need EOMONTH+1

 

[Eric W]

Try:

=EOMONTH(A1,IF(DAY(A1)<=15,-1,0))+1

or slightly shorter:

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

 

[Gerald Higgins]

Sorry I misread the OP.


In the meantime Eric W's suggestions probably work, and are neater than mine.

 

[bobletcs123]

Thanks guys... Eric, worked like a charm.
Thanks for all the help Gerald & Eric; much appreciated

 

[Eric W]

Glad we could help.

 

[Tetra201]

@ Eric W:

Or even shorter

=EOMONTH(A1-15,0)+1