Convert NOW() to Text

[excelbytes]

I have a situation where if I enter a value of "Yes" in one cell, I want the date and time to populate another cell in that row, but be fixed and not refresh when any other entry is made in that worksheet.

I tried something like this in cell L6:

=IF(K6="Yes",TEXT(NOW(),"mm/dd/yyyy hh:mm:ss"),"")

But it keeps updating whenever any other data is entered in the worksheet like any volatile function would.

Is there a non-VBA solution to this?

 

[Rick Rothstein]

As far as I know, the only way to do what you want is to have VBA place the value as text (a formula cannot do this) into the cell.

 

[excelbytes]

Actually, there is a solution, sort of.

=IF(K2="Yes",IF(L2="",NOW(),L2),"")

This creates a circular reference, so it requires checking the "Enable iterative calculation" in the Formulas section of the Options. Not a great solution, but one none the less.

 

[Rick Rothstein]

Actually, there is a solution, sort of.

=IF(K2="Yes",IF(L2="",NOW(),L2),"")

This creates a circular reference, so it requires checking the "Enable iterative calculation" in the Formulas section of the Options. Not a great solution, but one none the less.

I've not tried, but will that actually survive the workbook being closed and then reopened?