• 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

A

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

  • date formula.xlsx
    45.8 KB · Views: 6
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.
 
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?
 
vletm said:
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?
Click to expand...

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.
 
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
 
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.
 
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.
 
one more help required here
attached file, when I put anything in column "d" it freezes date in column "b"
is it possible if I delete entry in column "d", column "b" again convert in formula like (today"")
 

Attachments

  • date formula chandoo.xlsb
    26.6 KB · Views: 4
You must log in or register to reply here.
Back
Top