Formula To Post Date In A Cell The Day Another Cell Is Updated?

[Dhira]

How do I post a date in a cell to show the date that a value was entered into another cell?

=IF(AZ312>0,Today(),"")

The issue is I need "Today()" to remain 'printed' on the day it was calculated. So that the next day, it still shows the actual day the referenced cell was updated.

 

[etaf]

you will need to turn off iterations on the settings to avoid circular references

I dont normally post weblinks as a solution, BUT

its probably easier to explain with this article which has images for the settings for windows (as i have a Macbook and so different)

How to insert timestamps in excel sheet using formulas

Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it. Here is an excel formula trick to generate...

chandoo.org

Also - see article point 3 here

How to Insert a Timestamp in Excel [Formula + VBA + Shortcut]

In general, it contains the current date and time and we have different methods but, the thing is, which is the best way to insert a timestamp in Excel?

excelchamps.com

it also has a VBA solution, I do not provide VBA here

 

[Kaper]

Edit: I started writing VBA solution before above was postedn as iterations/circular references approach affects also other calculations.


The formula can't do that in standard way. Standard solution in this case would be to use VBA code - Sheet Change event.

If you can have macro enabled workbook then:
You may right-click on tab with sheet name, click on Display Code, paste the following in the window which shall be visible:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim acell As Range, rng As Range
Set rng = Intersect(Target, Range("AZ312:AZ412"))
If Not rng Is Nothing Then
  For Each acell In rng
    If acell > 0 And Range("BA" & acell.Row) = "" Then Range("BA" & acell.Row) = Date
  Next acell
End If
End Sub

Close VBA editor and retun to your workbook. Save it in macro enabled format (xlsm, xlsb). Close and reopen file making sure macros are enabled.

Try writing something in AZ312
BA312 shall show todays date (make sure this cell has a date format)

Comments:
I assumed the issue is not just for one cell but for many cells in one column (change range in line 3)
I assumed the date will be (in BA column - change it to proper column name in line 6 of the code
I assumed that "a value was entered" means manual entering, not the formula in AZ312 which calculated as >0
I assumed that once something is written in AZ the date in BA staus untouched even if next week you change value in AZ again. If that's not the case, and you want to record date of last change in AZ delete
And Range("BA" & acell.Row) = ""
in line 6

 

[Dhira]

Thanks,

you will need to turn off iterations on the settings to avoid circular references

I dont normally post weblinks as a solution, BUT

its probably easier to explain with this article which has images for the settings for windows (as i have a Macbook and so different)

How to insert timestamps in excel sheet using formulas

Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it. Here is an excel formula trick to generate...

chandoo.org

Also - see article point 3 here

How to Insert a Timestamp in Excel [Formula + VBA + Shortcut]

In general, it contains the current date and time and we have different methods but, the thing is, which is the best way to insert a timestamp in Excel?

excelchamps.com

it also has a VBA solution, I do not provide VBA here

I used the non-VBA solution. & formula came out to:

=IFS(AY312="","",AZ312<>"",AZ312,1*1,Now())

[Where the formula is placed in cell AY312]

 

[etaf]

you are welcome