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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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
1
Views
55
Replies
13
Views
133
Replies
4
Views
45
Replies
3
Views
348
Replies
17
Views
110