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

#### bobletcs123

##### New Member
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Gerald Higgins

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
Try:

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

or slightly shorter:

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

Last edited:

#### Gerald Higgins

##### Well-known Member

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

#### bobletcs123

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

#### Tetra201

##### MrExcel MVP
@ Eric W:

Or even shorter

=EOMONTH(A1-15,0)+1

Replies
5
Views
101
Replies
6
Views
452
Replies
7
Views
464
Replies
0
Views
151
Replies
6
Views
226

1,195,598
Messages
6,010,646
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back