Date Correction

  • Thread starter Thread starter JPS
  • Start date Start date
J

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…
 
=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)
 
2 methods

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

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

Mike
 
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)
 
Bob,
U DA MAN,
Thanks....
--
JPS


Bob Phillips said:
=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)
Click to expand...
 
Back
Top