automatic time stamp in excel

[zico martin 89]

I know there's a few excel experts in here so Im hoping someone can help me set this up ( and explain how in laymens terms of course!).

 

What I require is that when I add a value to a cell in column B that the time and date are automatically added to the adjacent cell in column A. Ideally this will then not be changed when I amend any details in any of the cells on that row. I've had a quick look on the net and though it appears it can be done I really cant fathom how to go about it

 

thanks in advance?

[Craig 6670]

The syntax for the current date and time to be stamped into a cell is

=now()

 

All you will have to do I'm guessing is build an IF clause on the basis of that. i.e. if the cell in column B changes, then =now() is applied to the corresponding cell in column A.

 

If I get chance later, I'll try and knock up an example.

[zico martin 89]

The syntax for the current date and time to be stamped into a cell is

=now()

 

All you will have to do I'm guessing is build an IF clause on the basis of that. i.e. if the cell in column B changes, then =now() is applied to the corresponding cell in column A.

 

If I get chance later, I'll try and knock up an example.

 

thanks I'd appreciate that

[MrBass 2594]

If you're okay with the date/time stamp updating whenever you update the values in column B then this'll do the job

=IF(B1="","",NOW())

 

You'll also need for format column A to be the date/time format you require.

[zico martin 89]

If you're okay with the date/time stamp updating whenever you update the values in column B then this'll do the job

=IF(B1="","",NOW())

 

You'll also need for format column A to be the date/time format you require.

 

 

thanks but i cant get that working for me. I insert this formula into the relevant cell but it appears to enter the date/ time whether there is a value in column B or not. Also for this to be of any use it would need to be fixed and not change when any of the values in the row are altered. Sorry to be a nag.

[peasepud 59]

I believe that this Subroutine will do what you want

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "A").Value = Int(Now)
End If
End With
Next Cell
End Sub

[zico martin 89]

I believe that this Subroutine will do what you want

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "A").Value = Int(Now)
End If
End With
Next Cell
End Sub

 

 

cool

 

 

so where do i put it? what do i do with it?

[peasepud 59]

I believe that this Subroutine will do what you want

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "A").Value = Int(Now)
End If
End With
Next Cell
End Sub

 

 

cool

 

 

so where do i put it? what do i do with it?

 

I havent got Excel to hand but I believe that if you go into Tools>Macros>Visual Basic Editor then you'll get two panes, the left hand side has a sort of navigation tree, click on the worksheet you're wanting to do it on and cthe right hand pane will go clear. Paste it in there to attach it to that Workbook.

[zico martin 89]

I believe that this Subroutine will do what you want

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "A").Value = Int(Now)
End If
End With
Next Cell
End Sub

 

 

cool

 

 

so where do i put it? what do i do with it?

 

I havent got Excel to hand but I believe that if you go into Tools>Macros>Visual Basic Editor then you'll get two panes, the left hand side has a sort of navigation tree, click on the worksheet you're wanting to do it on and cthe right hand pane will go clear. Paste it in there to attach it to that Workbook.

 

i must be doing something wrong as it seems to have no effect on anything! my lack of VB knowledge isnt helping me out much like!

[MrBass 2594]

If you're okay with the date/time stamp updating whenever you update the values in column B then this'll do the job

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "A").Value = Int(Now)
End If
End With
Next Cell
End Sub

 

 

cool

 

 

so where do i put it? what do i do with it?

 

I havent got Excel to hand but I believe that if you go into Tools>Macros>Visual Basic Editor then you'll get two panes, the left hand side has a sort of navigation tree, click on the worksheet you're wanting to do it on and cthe right hand pane will go clear. Paste it in there to attach it to that Workbook.

 

i must be doing something wrong as it seems to have no effect on anything! my lack of VB knowledge isnt helping me out much like!

 

At the bottom of your workbook, you should have different tabs for each sheet. If you haven't renamed them and still got your defaults, it'll be Sheet1, Sheet2, Sheet3.

 

Right-click on Sheet1 and, from the pop-up menu, select 'View Code' - This will launch the VBA editor.

 

By default, your current workbook should be the top window in the right pane - if it's a brand new workbook, the window title will be 'Book1 - Sheet1 (Code)'

 

You'll have two dropdown menus at the top, the left one will be defaulted to '(General)' and the right one '(Declarations)'

 

Click the down arrow for the left dropdown menu and select 'Worksheet'

 

The right dropdown should default to 'SelectionChange', click the down arrow in the right dropdown and select 'Change'

 

The will already give you the 'Private Sub... ...End Sub' lines from Pud's code so you'll just need to paste the other lines inbetween.

 

Once done, go back to your workbook and test!

 

Edit, if you also want the timestamp recorded, then instead of 'Int(Now)', just use 'Now'

Edited January 20, 2009 by MrBass

[zico martin 89]

somehow this is working now, thanks mr bassman

 

however i can only get the date input into column A - how about the time as well?

[MrBass 2594]

Which method are you using - Pud's super method or my simple one?

 

Either way, you'll need to make sure column is formatted correctly, right-click, Format Cells..., select the Custom category and in the Type: field, enter DD/MM/YYYY HH:MM:SS or whatever format you like.

[zico martin 89]

Which method are you using - Pud's super method or my simple one?

 

Either way, you'll need to make sure column is formatted correctly, right-click, Format Cells..., select the Custom category and in the Type: field, enter DD/MM/YYYY HH:MM:SS or whatever format you like.

 

your method i thinki, i right clicked the worksheet and input the code that way.

 

I've formated the cells now to include the time but it always comes up as 00:00

[MrBass 2594]

Which method are you using - Pud's super method or my simple one?

 

Either way, you'll need to make sure column is formatted correctly, right-click, Format Cells..., select the Custom category and in the Type: field, enter DD/MM/YYYY HH:MM:SS or whatever format you like.

 

your method i thinki, i right clicked the worksheet and input the code that way.

 

I've formated the cells now to include the time but it always comes up as 00:00

 

That's Pud's method and:

Edit, if you also want the timestamp recorded, then instead of 'Int(Now)', just use 'Now'

[zico martin 89]

Which method are you using - Pud's super method or my simple one?

 

Either way, you'll need to make sure column is formatted correctly, right-click, Format Cells..., select the Custom category and in the Type: field, enter DD/MM/YYYY HH:MM:SS or whatever format you like.

 

your method i thinki, i right clicked the worksheet and input the code that way.

 

I've formated the cells now to include the time but it always comes up as 00:00

 

That's Pud's method and:

Edit, if you also want the timestamp recorded, then instead of 'Int(Now)', just use 'Now'

 

 

oh yeah thanks mate. working like a dream now

[zico martin 89]

quite happy with it working now but I think it would be nice if I could freeze the contents of column A so that it wont change subsequently if I amend the values in column B - is this easy to do?