generate month end date

[Koolfala7]

Hi there

I want to create a spreadsheet where if i put a date for eg 21/09/2011 in the next column it tells me 30/09/2011. another eg is if i put 20/08/2011 in the next column it say 31/08/2011.what is the formula for this?the first column will have any date but the next column should generate the month end date.

Please help

Thanks

 

[barry houdini]

You can use EOMONTH function in Excel 2007 or later

=EOMONTH(A1,0)

where A1 contains a date

In earlier versions of Excel you can still use EOMONTH but it's part of the "Analysis ToolPak" add-in which needs to be enabled....otherwise you can use this version

=DATE(YEAR(A1),MONTH(A1)+1,0)

 

[jim may]

Excel Workbook
	A	B
1	9/21/2011	9/30/2011
2	8/20/2011	8/31/2011
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=EOMONTH(A1,0)
B2	=EOMONTH(A2,0)

 

[Koolfala7]

Thanks..it works..