How do you keep Today() from updating

Posted by Brian on October 15, 2001 4:33 AM

I have a spreadsheet where someone enters a sale price in cell A2, this in turn triggers a formula in another cell

=IF(A2>0, TODAY(), "Available")

But unfortunately whenever there's another update on the page, today() resets to the new date. Is there anyway I can get it to keep the date calculated when that cell was updated (not the whole sheet) without using a macro?

Posted by Dan on October 15, 2001 6:59 AM

There is not built in function to do this. As you found out, TODAY() always returns the current date. Depending on your needs, one workaround is to let the formula do it's calculation, the Copy the cell, and choose Paste Special, and paste the Value back in to the cell. You will lose the formula and replace it with either the date or "Available".

Not sure if that helps...

Posted by Brian on October 15, 2001 7:12 AM

Dan,

Thanks for your reply. Is there any easy way to run the macro automatically, i.e. when someone enters a price in cell A2, Cell B2 using the formula
=IF(A2>0, TODAY(), "Available")
then automatically calculates todays date,
then could I have a macro that copies and then special pastes the date when that value changes?

Brian

Posted by Jonathan on October 15, 2001 9:41 AM

As long as you're going to use a macro, there's a fairly simple way to 'freeze' a timestamp. Ordinarily I use an empty cell which then gets the timestamp, and I code it like this:

If Range("A1").Value = "" then Range("A1").Value = Date

"Date" is the VBA equivalent of the worksheet'Today()'

But this would only work if the cell is either empty or already has a date in it. I notice in your example the cell says "Available" until it has a date it in. So let's rewrite the code like this:

If Range("A2") = "" Then Range("B2").Value = "Available"

If Range("A2").Value > 0 Then Range("B2").Value = Date

Now it says if A2 is empty (A2 = an empty string, signified by two doublequotes with no space between them), then B2 will say "Available". If A2 has a value in it, greater than 0, then B2 will have the date. This date will stick unless A2 becomes empty again.

There's further checking you might want to do. What if someone enters a negative number in A2? Then it's not empty but neither is it greater than 0, so you have to know exactly what it is you want.

You could put those two lines in the ThisWorkbook module of the workbook, in the Workbook_Open event handler. If this is Greek to you, feel free to ask for further info.

HTH