Getting a date six months back
 

I want to be able to enter a date in cell A1, and have cell B1 be the last
day of the month, six months earlier

So if I enter 7/18/2005, the date in B1 would be 1/31/2005.
8/3/05 would 2/28/05 (and would adjust if this were a leap year).

Can someone help me with this formula?

Thanks very much.

Daniel

There may be a simpler way but this formula worked for me. If you don't have
the EOMONTH function in your Excel, install the add in Analysis Toolpack by
following the menu path Tools / Addin's and choosing the appropriate option.

Try this formula:
=EOMONTH(IF(MONTH(G1)-6<1,DATEVALUE(CONCATENATE(MONTH(G1)+6,"/",1,"/",YEAR(G1)-1)),DATEVALUE(CONCATENATE(MONTH(G1)-6,"/",1,"/",YEAR(G1)))),0)

Substitute your cell for G1.

Hope this helps.

Thanks,
Bill Horton

"Daniel Bonallack" wrote:

I want to be able to enter a date in cell A1, and have cell B1 be the last
day of the month, six months earlier

So if I enter 7/18/2005, the date in B1 would be 1/31/2005.
8/3/05 would 2/28/05 (and would adjust if this were a leap year).

Can someone help me with this formula?

Thanks very much.

Daniel

On Wed, 3 Aug 2005 11:02:03 -0700, "Daniel Bonallack"
wrote:

I want to be able to enter a date in cell A1, and have cell B1 be the last
day of the month, six months earlier

So if I enter 7/18/2005, the date in B1 would be 1/31/2005.
8/3/05 would 2/28/05 (and would adjust if this were a leap year).

Can someone help me with this formula?

Thanks very much.

Daniel


=EOMONTH(A1,-6)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.


On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

Bill, Ron, thanks a lot.

Daniel

"Ron Rosenfeld" wrote:

On Wed, 3 Aug 2005 11:02:03 -0700, "Daniel Bonallack"
wrote:

I want to be able to enter a date in cell A1, and have cell B1 be the last
day of the month, six months earlier

So if I enter 7/18/2005, the date in B1 would be 1/31/2005.
8/3/05 would 2/28/05 (and would adjust if this were a leap year).

Can someone help me with this formula?

Thanks very much.

Daniel


=EOMONTH(A1,-6)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.


On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

On Wed, 3 Aug 2005 13:58:01 -0700, "Daniel Bonallack"
wrote:

Bill, Ron, thanks a lot.

Daniel


You're welcome. Glad to help.


--ron