General discussion

  • Creator
    Topic
  • #2125382

    How to change a cell “Date” in Excel?

    Locked

    by fullyfamous ·

    I have a Excel spreadsheet to do a daily cash receipt report. Each month there is a different “reporting period”, i.e. Jan 2002, Feb 2002. Currently I manually change this cell at the beginning of each month to reflect the current month. How would Iformat this cell to automatically change to the current month?
    Thank You

All Comments

  • Author
    Replies
    • #3450012

      How to change a cell “Date” in Excel?

      by razzadaz ·

      In reply to How to change a cell “Date” in Excel?

      Try:
      Insert>Function>Date&Time>Date(or function name of your preference, such as month -date and year.

    • #3449998

      How to change a cell “Date” in Excel?

      by shanghai sam ·

      In reply to How to change a cell “Date” in Excel?

      =NOW() with the cell formatted as MMMM, YYYY supplies the present month, but it’s volatile; it will change as the date changes. You would have to Copy and Paste Special Values to make it permanent.

      You could write a simple macro that you would run each month:

      Sub TheMonth
      Dim MyDate
      MyDate = Date
      Selection = MyDate
      Selection.NumberFormat = ?MMMM, YYYY?
      End Sub

      If you name the sheets with the date at the beginning of the year, you can put the sheet name in the cell with:
      =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,
      LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1)))

Viewing 1 reply thread