Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

How to change a cell “Date” in Excel?

Locked

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

Topic

How to change a cell “Date” in Excel?

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.

How to change a cell “Date” in Excel?

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)))

[Author]

Replies