Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Cie
 
Posts: n/a
Default 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.
  #3   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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.



  #4   Report Post  
Cie
 
Posts: n/a
Default

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.




  #5   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert date automatically Rick Excel Discussion (Misc queries) 2 March 31st 05 11:49 PM
Automatically enter date and time but only update once. PM New Users to Excel 3 January 21st 05 08:47 AM
how do i make a date change automatically if i change one before . dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 10:21 PM
Function to automatically insert a new sheet as a result of data entry? Mark Mulik Excel Worksheet Functions 2 November 28th 04 03:21 AM
How to change (delivery) days and automatically the receive date in an other cell? Elboo Excel Worksheet Functions 5 November 22nd 04 03:44 PM


All times are GMT +1. The time now is 10:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"