General discussion

  • Creator
    Topic
  • #2316273

    formula for month end dates in Excel

    Locked

    by csuu ·

    How do I list the month end (or begin month) dates automatically (by formula)in a row or in a column? For example, Jan 31,2003; Feb 28,2003; etc.

All Comments

  • Author
    Replies
    • #3380379

      Reply To: formula for month end dates in Excel

      by dklippert ·

      In reply to formula for month end dates in Excel

      The place to look for “Date Math” is
      http://www.cpearson.com/excel/datetime.htm
      Here’s one suggestion. choose one cell to place the starting date, say A1.
      Enter the following formula in the first cell of the column where you want the dates displayed, say F1.

      =DATE(YEAR($A$1),MONTH($A$1)+ROWS($F$1:F1),0)
      Now grab the fill handle at the lower right corner and fill the formula down as far as it may be needed.

    • #3380361

      Reply To: formula for month end dates in Excel

      by sgt_shultz ·

      In reply to formula for month end dates in Excel

      my version of Excel (2000) has an EOMONTH function which returns the serial number of the end of the month. if you don’t have that function, you may have to install it, Tools/Add-ins.

      • #3380358

        Reply To: formula for month end dates in Excel

        by sgt_shultz ·

        In reply to Reply To: formula for month end dates in Excel

        here is the formula that will yield the end of the month date for January 03. change the last parameter (the month offset – the zero in this example) to 1 thru 11 for the rest of the months. remember to format the cells in the date format you want, else you just see a strange number..
        =EOMONTH(“01/01/03”,0) -put in January cell
        =EOMONTH(“01/01/03”,1) -put in february cell
        and so on…

      • #3380357

        Reply To: formula for month end dates in Excel

        by sgt_shultz ·

        In reply to Reply To: formula for month end dates in Excel

        oops. the thing you want to Add-In is the Analysis Pack….

      • #2744443

        Reply To: formula for month end dates in Excel

        by csuu ·

        In reply to Reply To: formula for month end dates in Excel

        Poster rated this answer.

    • #2741769

      Reply To: formula for month end dates in Excel

      by ananthap ·

      In reply to formula for month end dates in Excel

      In a cell, typing “date(2003,10,1) – 1” (without the quotes) will give you “September 30, 2001”. That is, the first day of the next month MINUS 1 day.

      End

    • #2744441

      Reply To: formula for month end dates in Excel

      by csuu ·

      In reply to formula for month end dates in Excel

      This question was closed by the author

Viewing 3 reply threads