Formula to Advance Date - same day next month or to the end of the month

[Steve=True]

Hi all,

Struggling for an easy formula.

Building a dashboard and looking to advance a user entered date

either 1 month or to the end of the next month.

For instance, i want to a formula that will take the user date of:

1/15/11 and advance it to 2/15/11

AND also take a user entered date of:

1/29/11 and advance it to 2/28/11

So adding 1 month won't work as it will give me 3/1/11. Was using a forum solution here that finds how many days in a month, but it can't get it to work for what i need:

=F2+DAY(DATE(YEAR(F2),MONTH(F2)+1,))

Looking for Non-VBA 2003 solution. Thanks!

Steve=True

 

[RSXchin]

I would think a custom function (not a macro) would be a perfect solution. Too bad it's VBA and you don't want to do that.

 

[barry houdini]

Hello Steve=True

If you don't mind using Analysis ToolPak add-in functions you can use EDATE, i.e.

=EDATE(F2,1)

or without add-ins

=MIN(DATE(YEAR(F2),MONTH(F2)+1+{1,0},DAY(F2)*{0,1}))

 

[Steve=True]

rsxchin - yeah, client requirement

Barry, thanks much, you are as always, a scholar and a gentleman.

Without addins worked perfectly! You are wonderful.

I have never seen { used in that way. Can you give a detailed explaination?

Thanks!

Steve=True

 

[barry houdini]

Effectively you want the minimum value between the formula you tried to add 1 month and the end date of the next month....so in long form that would be

=MIN(DATE(YEAR(F2),MONTH(F2)+2,0),DATE(YEAR(F2),MONTH(F2)+1,DAY(F2)))

The version I suggested just shortens that a little by creating an "array" of both of those dates...so in

=MIN(DATE(YEAR(F2),MONTH(F2)+1+{1,0},DAY(F2)*{0,1}))

The first date generated has the YEAR from F2, the MONTH from F2 +1 + the first element of {1,0}.....and DAY(F2) multiplied by the first element of {0,1}...so that gives you DATE(YEAR(F2),MONTH(F2)+2,0)

The second date created has the YEAR from F2, the MONTH from F2 +1 + the second element of {1,0}.....and DAY(F2) multiplied by the second element of {0,1}...so that gives you DATE(YEAR(F2),MONTH(F2)+1,DAY(F2))

 

[Steve=True]

Got it, thanks a ton!

Very creative!