Getting a date six months back

  • Thread starter Daniel Bonallack
  • Start date
D

Daniel Bonallack

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
 
W

William Horton

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
 
R

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
Click to expand...


=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
 
D

Daniel Bonallack

Bill, Ron, thanks a lot.

Daniel

Ron Rosenfeld said:
=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
Click to expand...
 
Top