Automatically Insert DATE, so that DATE will NOT change
 

I'm using the following formula
=IF(L8<"",TEXT(TODAY(),"mm/dd/yy"),"")

My problem is, everytime I go back to this spreadsheet, the date will change
to the current date. I DO NOT want the date to change. The date needs to
remain the same date as when the data was entered.

Then just copy/paste values or use ctrl+colon for date and semi colon for
time or both for date/time

--
Don Guillett
SalesAid Software

"Cie" wrote in message
...
I'm using the following formula
=IF(L8<"",TEXT(TODAY(),"mm/dd/yy"),"")

My problem is, everytime I go back to this spreadsheet, the date will
change
to the current date. I DO NOT want the date to change. The date needs to
remain the same date as when the data was entered.

Cie,

Then you need to use a worksheet change event to do that: for example, for
any cell in column L, the date when the entry is made is stored in column M
using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("L:L"))
myCell(1, 2).Value = Int(Now)
myCell.NumberFormat = "mm/md/yy"
Next myCell
Application.EnableEvents = True
End Sub

Copy this code, right-click on the worksheet tab, select "View Code" and
paste the code in the window that appears.
HTH,
Bernie
MS Excel MVP


"Cie" wrote in message
...
I'm using the following formula
=IF(L8<"",TEXT(TODAY(),"mm/dd/yy"),"")

My problem is, everytime I go back to this spreadsheet, the date will
change
to the current date. I DO NOT want the date to change. The date needs to
remain the same date as when the data was entered.

Bernie: Thanks for the information but, I don't understand what a Private Sub
Worksheet is nor the information that follows. I think the information that
follows is the formula which I can follow but, am still learning and need a
little more guidance.

Thanking you in advance.

"Bernie Deitrick" wrote:

Cie,

Then you need to use a worksheet change event to do that: for example, for
any cell in column L, the date when the entry is made is stored in column M
using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("L:L"))
myCell(1, 2).Value = Int(Now)
myCell.NumberFormat = "mm/md/yy"
Next myCell
Application.EnableEvents = True
End Sub

Copy this code, right-click on the worksheet tab, select "View Code" and
paste the code in the window that appears.
HTH,
Bernie
MS Excel MVP


"Cie" wrote in message
...
I'm using the following formula
=IF(L8<"",TEXT(TODAY(),"mm/dd/yy"),"")

My problem is, everytime I go back to this spreadsheet, the date will
change
to the current date. I DO NOT want the date to change. The date needs to
remain the same date as when the data was entered.

Cie,

The code is event code, which Excel runs when you change something on the
worksheet. Copy the code, right-click on the worksheet tab, select "View
Code" and paste the code in the window that appears.

Then whenever anything is entered or deleted in column L, the date will
appear as a value (so that it will never change due to having a formula like
the one that you used) will appear in column M.

It's an advanced technique, but sometimes it is the only way to do things
_automatically_, as you asked. Otherwise, simply type in the date when you
change cell L8, or use the date shortcut, or use your formula and copy and
paste values.

HTH,
Bernie
MS Excel MVP


"Cie" wrote in message
...
Bernie: Thanks for the information but, I don't understand what a Private
Sub
Worksheet is nor the information that follows. I think the information
that
follows is the formula which I can follow but, am still learning and need
a
little more guidance.

Thanking you in advance.

"Bernie Deitrick" wrote:

Cie,

Then you need to use a worksheet change event to do that: for example,
for
any cell in column L, the date when the entry is made is stored in
column M
using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("L:L"))
myCell(1, 2).Value = Int(Now)
myCell.NumberFormat = "mm/md/yy"
Next myCell
Application.EnableEvents = True
End Sub

Copy this code, right-click on the worksheet tab, select "View Code" and
paste the code in the window that appears.
HTH,
Bernie
MS Excel MVP


"Cie" wrote in message
...
I'm using the following formula
=IF(L8<"",TEXT(TODAY(),"mm/dd/yy"),"")

My problem is, everytime I go back to this spreadsheet, the date will
change
to the current date. I DO NOT want the date to change. The date needs
to
remain the same date as when the data was entered.