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

bobletcs123

New Member
Joined
Jan 31, 2019
Messages
7
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
 

Some videos you may like

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
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Jan 31, 2019
Messages
7
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
Joined
Aug 18, 2015
Messages
10,212
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
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

Sorry I misread the OP.


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

bobletcs123

New Member
Joined
Jan 31, 2019
Messages
7
Thanks guys... Eric, worked like a charm.
Thanks for all the help Gerald & Eric; much appreciated
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,124
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top