Getting a date six months back

[Guest]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[Guest]

Bill, Ron, thanks a lot.

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

 

[Ron Rosenfeld]

Bill, Ron, thanks a lot.

Daniel

You're welcome. Glad to help.


--ron