Inputting fixed time

R

Rich86

New Member
Joined
Jan 23, 2008
Messages
22
Hi i am new to the forum and have to say that it has alot of useful information and i have looked over all the different formulas and have not found one that works, so any help would be appericiated.

I have a sheet with a number of different columns, is there a way that when data is input into a cell in the first column that it time stamps the last column on the same row, and this time stamp is fixed. Then when data is input into the cell below the same thing happens, so i can have a time stamp for each entry.

Thank you very much
Rich
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Hi Rich

Right-click on the worksheet name where you want this to happen and select View Code. Paste the following into the code module that will open up:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Set rng = Intersect(Target, Range("A2:A1000")) 'amend as applicable
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each c In rng
        With Cells(c.Row, Columns.Count).End(xlToLeft).Offset(0, 1)
            .Value = Now
            .NumberFormat = "dd mmm yy hh:mm"
        End With
    Next c
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Rich86

Welcome to the MrExcel board!

You won't be able to do this with a formula, but you can do it with VBA. I suggest using the 'Search' facility near the top of the page and search for something like 'date or time stamp'. That should turn up lots of threads as similar questions have been asked many times. I have just picked one thread as an example:
http://www.mrexcel.com/forum/showthread.php?t=265174

Edit: Or, more simply, look at Richard's suggestion above that I had not noticed before I posted. :)
 
Last edited:
Upvote 0
Something like the below ?

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And IsEmpty(Target.Value) = False Then Target.Offset(0, 4) = Now()
End Sub

not the offset 4 is just random -- this would the column into which you want to put the stamp (ie 4 = D)

this would go into VBA against the worksheet itself.
 
Upvote 0
Thanks your prompt and helpful answers, these have worked perfectly and now have a worksheet that looks very smart, and works well.

Thanks a lot

Rich
 
Upvote 0
Sorry one more question on this, its all working fine. But if you delete out of the cells when you enter again it dosent work, Any ideas?
 
Upvote 0
Sorry i didn't make myself clear.

This is working fine but say for instance i typed into A2 and the time came into B2. But then if after i deleted what was in A2 and B2. If i type into A2 again no time comes up. Hope that helps

Thank you
 
Upvote 0
If you just want it in the next column over you should amend to:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Set rng = Intersect(Target, Range("A2:A1000")) 'amend as applicable
If Not rng Is Nothing Then
    Application.EnableEvents = False
    For Each cell In rng
        If cell.Value <> "" Then
            With cell.Offset(0, 1)
              .Value = Now
              .NumberFormat = "dd mmm yy hh:mm"
            End With
         Else
            cell.Offset(0, 1).ClearContents
         End If
    Next cell
    Application.EnableEvents = True
End If
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Time stamp using VBA
Replies
5
Views
913
data808
D
C
  • Solved
Count number of entries that have a date stamp of last week
Replies
6
Views
434
Joe4
Joe4
S
  • Question
Text to Columns formula or code
Replies
7
Views
256
SlightlyClueless
S
G
  • Question
List TOP 10 occurrences by Week/Month/YTD
Replies
0
Views
123
gldurand
G
D
  • Question
VBA + Excel or Webpage Screenshot along with DateTime stamp from task bar
Replies
0
Views
159
DDRO
D

Forum statistics

Threads
1,214,827
Messages
6,121,823
Members
449,049
Latest member
cybersurfer5000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top