I have a spreadsheet with 1000+ rows and I need to correct a date column,
This column should contain the last day of the month, but often does not.

I am in need of a formula that will check the month and correct to the last
calendar day, i.e. November would be 30 and December would be 31. I need to
retain the original month and year and correct only the day.

Thanks€¦

--
JPS

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

and copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JPS" wrote in message
...
I have a spreadsheet with 1000+ rows and I need to correct a date column,
This column should contain the last day of the month, but often does not.

I am in need of a formula that will check the month and correct to the
last
calendar day, i.e. November would be 30 and December would be 31. I need
to
retain the original month and year and correct only the day.

Thanks.

--
JPS

2 methods

=EOMONTH(A1,0)
Requires the analysis toolpak Addin

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

Mike

"JPS" wrote:

I have a spreadsheet with 1000+ rows and I need to correct a date column,
This column should contain the last day of the month, but often does not.

I am in need of a formula that will check the month and correct to the last
calendar day, i.e. November would be 30 and December would be 31. I need to
retain the original month and year and correct only the day.

Thanks€¦

--
JPS

If your current date is in A1 then use this formula with the relevant cell
formatting to show the date format you require. (If EOMONTH worksheet
function is not available then select Tools - Add-Ins and check Analysis
ToolPak)

=EOMONTH(A1,0)

"JPS" wrote:

I have a spreadsheet with 1000+ rows and I need to correct a date column,
This column should contain the last day of the month, but often does not.

I am in need of a formula that will check the month and correct to the last
calendar day, i.e. November would be 30 and December would be 31. I need to
retain the original month and year and correct only the day.

Thanks€¦

--
JPS

Bob,
U DA MAN,
Thanks....
--
JPS


"Bob Phillips" wrote:

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

and copy down

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"JPS" wrote in message
...
I have a spreadsheet with 1000+ rows and I need to correct a date column,
This column should contain the last day of the month, but often does not.

I am in need of a formula that will check the month and correct to the
last
calendar day, i.e. November would be 30 and December would be 31. I need
to
retain the original month and year and correct only the day.

Thanks.

--
JPS