1

I have an excel formula that I am using to automatically add the date to the cell next to the cell.

I use a drop down menu to select a value. Is there a way of then stopping this date from changing? At this stage the date comes up correctly, but then of course the next day, when I open it up again, then date displays the current day. Not so good as I am using this in a purchase order system and this will mean when I check on orders it will look like they are all ordered on the same date

1
  • Would you please add the formula? Is the drop-down implemented in VBA or using a range of allowable choices?
    – cxw
    Jun 30, 2015 at 10:01

1 Answer 1

0

If you use a function like NOW() or TODAY() in a formula, it will always update to the current time or date. That is the nature of any formula: it evaluates the current situation and returns a result based upon that.

NOW() will always return the current date and time and TODAY() will always return the current date. In order to ensure that these functions adjust to the current time, they are volatile, which means that they will re-calculate every time ANY cell in your workbook is changed, whereas non-volatile functions like Sum() will only re-calculate when cells that contribute to their result have changed.

If you want to put a time stamp against a data entry and you do not want that time stamp to change, you either have to enter it manually or use VBA to write the date/time in the next cell.

There are hundreds of sample on the web and on this site. Search for "time stamp" or "date stamp" with Excel as the other keyword. If you get stuck, pipe up.

In case you do not want to go down the VBA track, here are some handy keyboard shortcuts:

Ctrl-; will return the current date

Ctrl-Shift-; will return the current time. You can also think of that as Ctrl-:

If you want to quickly enter a stamp with time AND date into a cell, use

Ctrl-;-space-Ctrl-Shift-;

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .