Automatic date entries on spreadsheet.

jo54321 · Jul 10, 2011

Can anyone help, I'm creating a spreadsheet to enable people to submit overtime availibility each individual has a paeeword procted row on which they enter their availibility.
I would like to use a first come first served basis and thus need help with a formula to automatically date stamp when an entry is made.

I have tried the following formula which does what i require only it updates itself on a daily basis and so doesn't gove me the actual date of entry.

[FONT=&quot]=IF(COUNTA(B4:D4),IF(E4<>"",E4,NOW()),"")

The above formula relates to a group of cells Am PM Eve in which an entry could be made, E4 is the date of entry cell.

Is there a way to stop the date updating on a daily basis and thus staying as the date on which the entry was actually made.

Thanx J

[/FONT]

Smitty · Jul 10, 2011

You can use a Change event. The following example will put a date stamp in column B whenever an entry is made in column A:

Private Sub Worksheet_Change(ByVal Target As Range) ' Code goes in the Worksheet specific module Dim rng As Range ' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3") Set rng = Target.Parent.Range("A:A") ' Only look at single cell changes If Target.Count > 1 Then Exit Sub ' Only look at that range If Intersect(Target, rng) Is Nothing Then Exit Sub ' Action if Condition(s) are met (do your thing here...) Target.Offset(, 1).Value = Date End Sub

HTH,

SeanE · Jul 10, 2011

Hello,

I have come up with some code that I think should do what you require (if I've understood you correctly). There might be a better way of doing it, but at least this works.

The code does two things, you can either keep both, or remove which ever one you don't want. It will put the timestamp in the cell that was edited as a comment (hover over the cell to get the time), and it will put the timestamp in column E of whichever row was edited.

Code:

Public LastCell As Range

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Dim c As String
    c = Now

    If LastCell.Column >= 2 And LastCell.Column <= 4 Then
        LastCell.AddComment c
        Cells(LastCell.Row, 5).Value = c
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set LastCell = ActiveCell
End Sub

The code will need to be placed into the 'ThisWorkbook' object in the VBA editor (accessed via Alt-F11 whilst in the workbook).

If you don't want the comments part, just delete the "LastCell.AddComment c" line. (Likewise if you no longer want the timestamp in column E, delete "Cells(LastCell.Row, 5).Value = c" from the code).

Now, if your workbook is a shared workbook, I have no idea if this will behave any differently if more than two users are in using the workbook at any time.

Hope this works for you.

jo54321 · Jul 11, 2011

Many thanks both i will try these suggestions. J

rangerrambo · Jul 30, 2011

I need something similar but instead

I have A B C D E F H G

in G i want the automatic update on the date, when I change either A to H cells.

Hope its clear enough.

wigi · Jul 30, 2011

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then _
        If Target.Column = 8 And Target.Column <> 7 Then Cells(Target.Row, 7) = Date
End Sub

Automatic date entries on spreadsheet.

jo54321

New Member

Excel Facts

Smitty

Legend

SeanE

New Member

jo54321

New Member

rangerrambo

New Member

wigi

Well-known Member

Similar threads

Forum statistics

Share this page

Automatic date entries on spreadsheet.

jo54321

New Member

Excel Facts

Smitty

Legend

SeanE

New Member

jo54321

New Member

rangerrambo

New Member

wigi

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock