I have not found any way to easily auto-fill a column with month end dates -- i.e. December 31st, Janaury 31st, February 28th, March 31st. I have figured out a work around which is auto fill a series with the first of each month and subtract 1 day.
Is there a better way?
Gordon
Atlanta, GA
USA
iMac, OS X Mountain Lion (10.8.2)
Hi Twocybers,
Column A contains cells formatted as Date-and-Time, with the Time hidden. Any day of the month will do because
=EOMONTH(A2,0)
calculates the end of that month. Year in Column A will be important in February (Leap Years 😉).
The zero in the formula means zero offset (i.e. that month). See the Function Browser in Numbers and look up EOMONTH.
Format cells using Inspector > Cells > Cell format.
The Numbers User Guide, from the Help Menu in Numbers, is a good read.
Regards,
Ian
Gordon,
Your approach is the as simple a one I can imagine. There may be slightly easier ways to Fill and to write the expression for end of month, but it's all very basic. You may not have considered how you will eliminate the column with the first of the month dates. If you Select the end of month calculation column, Copy, and Paste Values, you can then safely delete the first of month column.
Jerry
Hi Gordon,
This is a variation on what Ian has suggested. It eliminates the need for the second column.
Cell A2 contains the initial date.
A3 contains the formula =EOMONTH(A2,1)
The formula is filled down through the rest of column A.
As can be seen in the second and second last rows containing dates, leap year is not an issue.
As Jerry has noted, you may want to replace the formulas with the calculated dates, To do so:
Regards,
Barry
Autofill month end dates
