3 Replies Latest reply: Jul 8, 2013 10:56 PM by Barry
Twocybers Level 1 Level 1 (10 points)

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)
  • Yellowbox Level 5 Level 5 (6,680 points)

    Hi Twocybers,

     

    Screen Shot 2013-07-04 at 10.43.57 PM.png

     

    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

  • Jerrold Green1 Level 7 Level 7 (29,925 points)

    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

  • Barry Level 7 Level 7 (29,210 points)

    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.

    Screen Shot 2013-07-08 at 10.49.16 PM.png

    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:

    • Click any cell in the table to show the column and row reference tabs.
    • Click the reference tab for column A to select that column.
    • Copy.
    • Go Edit > Paste Values.

     

    Regards,

    Barry