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
donaldb@281.com
"Cie" <cedcolo.MsE@yahoo.com> wrote in message
news:9ABB2930-99AB-4D69-94CC-7BDC09D3126A@microsoft.com...
> 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" <cedcolo.MsE@yahoo.com> wrote in message
news:9ABB2930-99AB-4D69-94CC-7BDC09D3126A@microsoft.com...
> 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" <cedcolo.MsE@yahoo.com> wrote in message
> news:9ABB2930-99AB-4D69-94CC-7BDC09D3126A@microsoft.com...
> > 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" <cedcolo.MsE@yahoo.com> wrote in message
news:B1F60A81-B8CD-4D8A-8C4D-501A97A7BED5@microsoft.com...
> 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" <cedcolo.MsE@yahoo.com> wrote in message
> > news:9ABB2930-99AB-4D69-94CC-7BDC09D3126A@microsoft.com...
> > > 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.
> >
> >
> >

Is there not something that you can type in a formula to do the same as the cntrl + ; shortcut?? Something that could be put in an if statement as if the value of the cell was changed to a 'C' then it would perform the cntrl + ; shortcut for you???

Okay, I like the above code, but how would it look if a certain cell was changed from "pending" to "complete". In otherwords.... "pending" is there now and when the cell is changed from "pending" to "complete" the date then is input in the following cell.

Also, How would I change the it so that if I changed the value on Worksheet(1), then the date would be input on Worksheet(2)?