insert a date that do not update

Question

Hi community,

Is there a formula / function (No Macro or User-defined Function) that can be inserted  to give a date (do not automatically updated) into a cell.

Example:

I am using a IF function in a cell, when the cell detected a value in the next adjacent cell, the cell (with the IF function) will display a current date (not suppose to be able to auto-update).

I am using Office 2010 Excel

Appreciated if there is any assistance rendered :)

Best wishes to all at Community :)

Answer 1

Event code is not all that hard to do. For example, if you wanted the date in the cell to the right of any single cell that changes on a sheet when it is changed, copy this code, right-click the sheet tab, select "View Code" and paste the code into the window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

With Target(1, 2)

'Choose any date/time format you like

.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"

.Value = Now

End With

Application.EnableEvents = True

End Sub

Answer 2

Hi,

appreciated your reply and thanks :)

regards,

thanks community as well :)

Answer 3

Hi,

Try this

1. Suppose you are entering the IF Function in column A, say in range A2:A50 (I am assuming that the IF() function will not return a blank).  Once data is entered, filter the column without blanks i.e. in the Filter drop down, uncheck Blanks
2. Select the blanks cells of the adjacent column and press Alt+; to select visible cells only
3. Press Ctrl+; to generate today's date

Hope this helps.

Answer 4

Hi,

emm . . . that is too much for me :), alright! accepted the info and appreciated your replied.  :)

regards,

best wishes to You and community group :)

Answer 5

No - you need to use a macro (event code, actually) to do that.