end of month date function

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have a list of date in an excel column, and I would like
to use a function in the next column that will give me the
last day of the month. For example, if I show 3/4/2003, I
would like function that will convert 3/4/2003 to
3/31/2003. I am most concerned about the month of
February where the last day of the month could be 28 or 29
depending on leap year.

Thanks,
Joe
 
Hi Joe!

Microsoft has a very neat solution to this. The zeroth of any month is
the last day of the preceding month:

=DATE(YEAR(A1),MONTH(A1)+1,0)
returns last day of month in A1

We also use this feature to determine how many days in any month:

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

And this week's gift to anal retention; two exceptions!

=DATE(1900,3,0) returns 29-Feb-1900 which didn't exist.
=(DATE(1900,1,0)) returns 00-Jan-1900 which is an anomaly.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top