Adding a date when something was created.

[Aikisteve]

Is there a way to have excel add the date that some data was entered inton a row.

I was wanting it to go at the end of the info that has been entered.

Thanks
Steve

[Aikisteve]

Just got it, i used a TODAY() function nested with an IF function. cant believe i didn't think of that straight away.

[Aikisteve]

Okay, this thread is getting rather 1 sided, BUT. I just tried changing the date on the clock on my computer to 11th to check if my new function worked. but it changed all the existing dates to the 11th, will this continue to happen, or is it just because i cheated and changed the date early.

[redstang423]

Unfortunately, if you simply use the "Today" command, it will return the date of the current day you are looking at the sheet. The IF functions should be able to take care of it if you use circular references, but I'd have to sit and think for a while on how to do it.

[Bernie Deitrick]

Steve,

You need to use a worksheet change event to do that: for example, for a change made to
any cell in column B, the date when the entry is made or changed is stored in column A
using this code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("B:B"))
Cells(myCell.Row, 1).Value = Now
Cells(myCell.Row, 1).NumberFormat = "mm/dd/yy hh:mm:ss"
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



"Aikisteve" <[email protected]> wrote in message
news:[email protected]...
>
> Is there a way to have excel add the date that some data was entered
> inton a row.
>
> I was wanting it to go at the end of the info that has been entered.
>
> Thanks
> Steve
>
>
> --
> Aikisteve
> ------------------------------------------------------------------------
> Aikisteve's Profile: http://www.excelforum.com/member.php...o&userid=36111
> View this thread: http://www.excelforum.com/showthread...hreadid=570319
>

[Aikisteve]

Hi Bernie, that works brilliantly, thanks ever so much, IS there a way for that to display the date in the cell rather than the formula bar. Also can it be made to display in the 9th column rather than the first?

Thanks again
Steve

[Aikisteve]

Ignore that last one bernie, the cotton woll fell off my brain and i saw what i was doing wrong.

Thanks it works brilliantly.

Steve