=now()

[Trevor3007]

hi,

is it possible to use =now() so it does not change the date on all previous entries to the current day?

Example

insert text into cell A2,and cell B2 has the =now() and returns 28/3/19

I reopen the file another day ( ie 3/4/19) and now find that the text I entered previously in cell A2, cell B2 now has the date of 3/4/19.


Can this be resolved to retain the original date ?


KR
Trevor3007

 

[Scott T]

A date stamp would require VBA or the user to enter the date. NOW always will return the current date time as of when it was calculated. If you need help with VBA post what you need to happen and someone can help you.

 

[Indystick]

To give you and idea what he means, this is how I approached the problem to applying a timestamp upon closing the file:

                    'Write Timestamp
                        Worksheets("MySheet).Activate
                        If Range("XFD1048576").Value < 1 Then
                            Sheets("MySheet").Unprotect Password:="password"
                            Range("I3").Value = Now
                            Range("XFD1048576").Value = 1
                            Sheets("MySheet").Protect Password:="password"
                        End If

AXFD1058576 is the last cell in the sheet, and I use it to keep a placeholder value that I can test against (e.g., if I've already entered a timestamp, the value will be 1). I imagine you could do something similar with other triggers.

 

[Fluff]

A word of caution, placing a value in the very last cell of a sheet can cause the entire workbook to slow down dramatically.
And can cause all sorts of other problems, best to be avoided.

 

[Trevor3007]

thank you for your reply.

The sheet is called 'EU Training'( sheet 2) the date is in the a2:A1000 & this is triggered when any data in the range B2:B1000 is entered.

I edited your code but due to my lack of VB knowledge, it just returned errors...any chance you can sort?

MTIA

KR Trevor3007

 

[Trevor3007]

Sorry Scott T, I was not sure if it was VB or a formula hence why I did not state.

Thanks anyhoos
Trevor307

 

[Trevor3007]

Cheers Fluff,

I dont have a scooby ,but hopefully some kind person will pick up your words of advance & sort accordingly.

KR
Trevor3007

 

[Scott T]

This will put the date in column A when column B is change

Right click on the EU Training tab and select view code. Past the code below in the VBA editor.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
    Target.Offset(0, -1) = Date 'if you want the date and time then use Now instead of Date
End If

End Sub

 

[Indystick]

This advice about not using the last cell is good advice, and you should take it.

My application is very small and so it hasn’t presented any problems for me; but that is probably just dumb luck.

 

[Trevor3007]

thanks Scott T,

Works great , apart from if I remove the data within B2:B1000, the date stays?


KR
Trevor3007