• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula TODAY() freezes date

Ateeb Ali

Member
Dear Sir,
I am using following formula in column B (fILE ATTACHED).
=+IF(D3<>0,TODAY(),"")

I want, when enter value in column D3 (for example), Column B3 should display today's date and then it freeze so its not keep on changing
 

Attachments

Chihiro

Excel Ninja
What you need is one of following.

1. Use circular reference (i.e. iterative calculation). See Chandoo.org article for detailed explanation.
https://chandoo.org/wp/timestamps-excel-formula-help/

2. I assume you don't want to update timestamp after initial data entry. You can just use CTRL +Semicolon ";".

If you need to update date based on some condition... you will want to use VBA. Exact code will depend on specific use case.
 

vletm

Excel Ninja
Ateeb Ali
What freezes? How do You see it?
> Why do You have there 2004 'today()'s?
>> Could eg You write to cell B1 =today() and to cell B3 =if(d3<>0,b$1,"") and copy down as use ... less today's?
 

Ateeb Ali

Member
Ateeb Ali
What freezes? How do You see it?
> Why do You have there 2004 'today()'s?
>> Could eg You write to cell B1 =today() and to cell B3 =if(d3<>0,b$1,"") and copy down as use ... less today's?
Dear Sir
I just mean, its a big file and user need to do entry daily basis with many inventories so I want when user mention inventory detail in column D.
Column B should display the date they are working in, like today is 20th March 2020, if they enter today, it should display today's date.

I made it done through TODAY() formula but the problem is =, when we open file tomorrow, the date changes to 21st March so I wanted to freeze that that once its entered, it should not change.
 

AlanSidman

Active Member
Using VBA, Insert the following in the Worksheet_Change Event
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim trow As Long
    If Target.Column = 4 Then
        trow = Target.Row
        Range("B" & trow) = Date
    End If

End Sub
 

vletm

Excel Ninja
Ateeb Ali
You wrote something, but You maybe mean something else ... hmm?
Do You 'mean' like:
two days ago some B-column values should be =Today() 18-Mar-2020, if some D-column values has changed
yesterday some B-column values should be =Today() 19-Mar-2020, if some D-column values has changed
today some B-column values should be =Today() 20-Mar-2020, if some D-column values has changed
For Excel ... formula ... =Today() will always be today.
Above could take care with VBA, but ... first ... You should really know - what do You need?
Update Your file with sample results, which You needs without any ... today() -formulas ... and upload it here.
 

vletm

Excel Ninja
Ateeb Ali
If You need that ... then okay.
That do not 'freeze' dates, but it won't change all B-column values.
If You write again something to already filled D-column cell, then it will change B-column date.
 
Top