Hi Everyone,

I adapted this formula from the 'date plus 1 month' question.

=MIN(DATE(YEAR(D17),MONTH(D17)+{-1,-3},DAY(D17)*{0,1}))

The only problem is the {0,1} in the days. When I input 7/31/06 into D17, I
get 5/1/06 as my result, when I want to get 4/30/06.

Also when I input 5/31/06 into D17, I get 3/3/06, when I want to get 2/28/06.

Any suggestions? Also, I have no idea what those {} brackets are
doing...anyone have an explanation?

Thanks.

Jessica

Try one of these

=MIN(DATE(YEAR(D17),MONTH(D17)+{-2,-3},DAY(D17)*{0,1}))

or (this one requires the Analysis Toolpak addin to be enabled)
=EDATE(D17,-3)


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Jessica" wrote:

Hi Everyone,

I adapted this formula from the 'date plus 1 month' question.

=MIN(DATE(YEAR(D17),MONTH(D17)+{-1,-3},DAY(D17)*{0,1}))

The only problem is the {0,1} in the days. When I input 7/31/06 into D17, I
get 5/1/06 as my result, when I want to get 4/30/06.

Also when I input 5/31/06 into D17, I get 3/3/06, when I want to get 2/28/06.

Any suggestions? Also, I have no idea what those {} brackets are
doing...anyone have an explanation?

Thanks.

Hi Jessica, try this:

=EOMONTH(E9,-3)

"Jessica" wrote:

Hi Everyone,

I adapted this formula from the 'date plus 1 month' question.

=MIN(DATE(YEAR(D17),MONTH(D17)+{-1,-3},DAY(D17)*{0,1}))

The only problem is the {0,1} in the days. When I input 7/31/06 into D17, I
get 5/1/06 as my result, when I want to get 4/30/06.

Also when I input 5/31/06 into D17, I get 3/3/06, when I want to get 2/28/06.

Any suggestions? Also, I have no idea what those {} brackets are
doing...anyone have an explanation?

Thanks.

THANKS! Ron & Paul. Unfortunantly I can't use the tool pack as I am sending
this to a client with limited excel knowledge! Ron's formula worked
perfectly!

"Jessica" wrote:

Hi Everyone,

I adapted this formula from the 'date plus 1 month' question.

=MIN(DATE(YEAR(D17),MONTH(D17)+{-1,-3},DAY(D17)*{0,1}))

The only problem is the {0,1} in the days. When I input 7/31/06 into D17, I
get 5/1/06 as my result, when I want to get 4/30/06.

Also when I input 5/31/06 into D17, I get 3/3/06, when I want to get 2/28/06.

Any suggestions? Also, I have no idea what those {} brackets are
doing...anyone have an explanation?

Thanks.