=now()

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Mar 2, 2018
Messages
60
To give you and idea what he means, this is how I approached the problem to applying a timestamp upon closing the file:

Code:
                    '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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,096
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 26, 2017
Messages
514

ADVERTISEMENT

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
 
Last edited:

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
Sorry Scott T, I was not sure if it was VB or a formula hence why I did not state.

Thanks anyhoos
Trevor307
 

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514

ADVERTISEMENT

Cheers Fluff,

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

KR
Trevor3007
 
Last edited:

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Code:
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

Board Regular
Joined
Mar 2, 2018
Messages
60
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.
 
Last edited:

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
thanks Scott T,

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


KR
Trevor3007
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,139
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top