Auto Date

[berg891832]

Is it possible that when I type data into Cell A1, the date will automatically appear next to it in cell B1? I want this down the column.

the date has to be static. I tried this =if(isempty(a1),"",today()). This works but teh date changes everyday to reflect the current date.

Bill

 

[johnwhite]

thanks for the info so far - brilliant!

is there a way to augment this formula so that it can also record the most recent date any cell other than the first in a row was modified?

for example, using the code above, i have cell B recording the date that data was entered in cell A (ie date created).

as my spreadsheet is a working document i would really also like to be able to record in cell H the last date any data in cells C-G was changed.

Seeing as cell A will never be changed once entered i wondered whether the above code could be ammended to read:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3:7 Then
Application.EnableEvents = False
Target.Offset(0, 5).Value = Date
Application.EnableEvents = True
End If
End Sub

but i can't get it to work because my coding skills are limited!

thanks in advance for your help,

john

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 3 And Target.Column <= 7 Then
    Application.EnableEvents = False
    Range("H" & Target.Row).Value = Date
    Application.EnableEvents = True
End If
End Sub

 

[johnwhite]

You, sir,

are a gentleman and a scholar.

Brilliant, exactly what I needed. Many thanks for your help!

John

 

[CarlosAntenna]

I have a similar situation. I need a static date based on the contents of another cell.

in cell I2: =IF(G2="stock",TODAY(),"")

Of course this changes with each recalc. Would anyone like to show me how?

--
Carlos

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 Then
    Application.EnableEvents = False
    If LCase(Target.Value) = "stock" Then Target.Offset(0, 2).Value = Date
    Application.EnableEvents = True
End If
End Sub

 

[CarlosAntenna]

Wooo Hooo it works!!!

Thanks for the instant response. It's more than I dared to expect.

It makes me wish I knew something about VBA.

--
Carlos

 

[Carnil]

Hi, all

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 3 And Target.Column <= 7 Then
    Application.EnableEvents = False
    Range("H" & Target.Row).Value = Date
    Application.EnableEvents = True
End If
End Sub

I've used an altered version of this code for a speadsheet I'm working on. It's a great bit of code but is there anyway that if someone trys to make a correction, it won't overwrite the date it has placed previously?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 3 And Target.Column <= 7 Then
    Application.EnableEvents = False
    If IsEmpty(Range("H" & Target.Row).Value) Then Range("H" & Target.Row).Value = Date
    Application.EnableEvents = True
End If
End Sub

 

[Carnil]

WOW you were really quick to get back to me on that one. Thanks.

However it doesn't seem to work. Maybe there's something I've done.
Here's my altered code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 2 And Target.Column <= 5 Or Target.Column >= 11 And Target.Column <= 16 Then
    Application.EnableEvents = False
    Range("V" & Target.Row).Value = Date
    Application.EnableEvents = True
End If
If Target.Column = 1 Then
    Application.EnableEvents = False
    If IsEmpty(Range("U" & Target.Row).Value = Date) Then Range("U" & Target.Row).Value = Date
    Application.EnableEvents = True
End If
End Sub

I'm trying to have one cell for when someone first started the job and the last time someone updated the data.

Thanks in advance for all your help.

 

[VoG]

Perhaps you mean

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column >= 2 And Target.Column <= 5 Then
    Range("V" & Target.Row).Value = Date
ElseIf Target.Column >= 11 And Target.Column <= 16 Then
    Range("V" & Target.Row).Value = Date
ElseIf Target.Column = 1 Then
    If IsEmpty(Range("U" & Target.Row).Value) Then Range("U" & Target.Row).Value = Date
End If
Application.EnableEvents = True
End Sub