Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a file with a VB macro that puts a TODAY() formula in a cell when I click a button. The formula is volatile and updates every time the sheet is calculated. Instead, I would like to lock the value so that it only shows the date on which the macro was executed. So, if I open the file tomorrow, it will be the same value. How can I do this?

share|improve this question
bAN is looking to prevent the Excel function =TODAY(), which is inserted into his sheet by a macro, from updating. Rather than =TODAY(), he's looking for a formula that will enter a date/time stamp and leave it. – Jared Harley Jul 30 '09 at 19:23
up vote 6 down vote accepted

Use the value property of the cell:

TheCell.Value = Date

Date = Today

share|improve this answer

You'll have to go in and modify the VBA code in the Click event off the button. I'd create a second button if you want Today's date in the cell, and disable it from the first button.

share|improve this answer

Quick way of doing this -

Select the cell and press Ctrl + ;

Another trick - Under the same conditions, if you want to insert the current time in HH: MM format:

Press Ctrl + : in the cell.

(FOR MAC: cmd + ;)

share|improve this answer
The question asks how this is done inside a macro, not from the keyboard. – Chenmunka Feb 12 at 15:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.