How to lock date in excel when with today function so it will not change when i open file on a new day

[BradyKeller11]

Hello!

Please help me out if you can....

This is my formula: =IF((G9)="x",(TODAY())," ")

It currently will add the date to the cell with this formula in it when i enter a X into cell G9 and and the formula cell will be blank with nothing in it.

I access this file almost daily and i want to keep the dates previously entered to not change as i enter other dates.

How can i lock dates with this formula so they will not change when i save and go back into the file at a later date?

 

[ghostlike]

hello there i have a similar problem
i need to indicate the time in witch a specific cell is changed so far i have try with the IF function but as i change the next cell it's updating the time on all the cells instead of only the one that i assigned
then i try to lock the cell when the first cell changes to prevent further changes on the time with " $ " sign no luck with that can someone help with that or it's too complicated ?

 

[Scott T]

Today returns today's date so when the date changes today will reflect the new date. To make a timestamp you will need VBA code. If you post more information on what you need then someone can help you with code.

 

[ghostlike]

i need whenever i change information on column "F" ( from F2 to F43 , i 'm counting equipment so the variety is numeric ) to show me at what time i have changed the volume of the equipment in a colmn "J" witch needs to be live so if i come back to the first cell and alter it to change the time instantly

 

[ghostlike]

that's the main frame i have to put the time whenever i check the location

 

[My Aswer Is This]

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now if you make any change to the range
F2 to F43

The current time will be loaded into same row column J

This will not enter todays date. Just the current time
If you wanted date and time change Time to Now

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/24/2019  1:54:59 AM  EST
If Not Intersect(Target, Range("F2:F43")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Target(, 5).Value = Time
End If
End Sub

 

[ghostlike]

many thanks mate . it's working perfectly i'll try to go thru it that in future i can make something like that on my own :P

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[Loera]

Hello,

I´ve got a very similar problem and tried customizing your code for it. Unfortunately it doesn´t work.
Would anybody be kind enough to troubleshoot, please?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Columns("B"), Target) Is Nothing Then Application.EnableEvents = False
For Each c In Intersect(Columns("B"), Target).Cells
If Not IsEmpty(c) Then
Cells(c.Row, "A").Value = Date
Else
If IsEmpty(c) Then Cells(c.Row, "A").Value = ""
End If
Next c
Application.EnableEvents = True
End If
End Sub


Thanks a lot in advance!
Loera

 

[My Aswer Is This]

Hello,

I´ve got a very similar problem and tried customizing your code for it. Unfortunately it doesn´t work.
Would anybody be kind enough to troubleshoot, please?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Columns("B"), Target) Is Nothing Then Application.EnableEvents = False
For Each c In Intersect(Columns("B"), Target).Cells
If Not IsEmpty(c) Then
Cells(c.Row, "A").Value = Date
Else
If IsEmpty(c) Then Cells(c.Row, "A").Value = ""
End If
Next c
Application.EnableEvents = True
End If
End Sub


Thanks a lot in advance!
Loera

This is a very old posting that I worked on as did others.
So explain in details what your trying to do

 

[Fluff]

In what way doesn't it work for you?