Date Correction

[JPS]

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…

 

[Bob Phillips]

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

 

[Mike H]

2 methods

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

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

Mike

 

[Dennis]

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]

Bob,
U DA MAN,
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)