finding the last day of the month

[Guest]

I have a spreadsheet that has a date column listing dates going back 3 years,
each month has about 50 line items with different days of the month, it there
a way to create a new column that lists the last day of the month for each
line item? For example if the date was August 9, 2007 the formula would
return August 31,2007.

Thanks

 

[Peo Sjoblom]

Assume the date is in A3

=DATE(YEAR(A3),MONTH(A3)+1,)

will return the date of the last day of the month for the date in A3



--


Regards,


Peo Sjoblom

 

[Ron Coderre]

With
A2: (a date)

If you have the Analysis ToolPak installed:
=EOMONTH(A2,0)

If you don't....then try this:
=DATE(YEAR(A2),MONTH(A2)+1,0)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Great solutions!! Thanks for the help...

With
A2: (a date)

If you have the Analysis ToolPak installed:
=EOMONTH(A2,0)

If you don't....then try this:
=DATE(YEAR(A2),MONTH(A2)+1,0)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Ron Coderre]

I'm glad I could help, Charles......Thanks for the feedback.

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)