stamp time in excel tables

K

karim elb

New Member
Joined
Jun 24, 2016
Messages
4
i am working with tasks in excel table -for example (row 1contains task -to answer i insert row 2 under the task above)-i want to - time stamp in D2
- i hope the answer with formulas -i am beginner in macro
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]
F
​
[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]task:how to stamp time ....
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]formula?[/TD]
[TD][/TD]
[TD]answer:..........[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

​
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thankful because i tried and read many ways without
usefulness
 
Sort by date Sort by votes
SlamberGamer said:
Hi there thank you very much for the code. it work.. but how can i set more than 1 cell example

Private Sub Worksheet_Change(ByVal Target As Range)




If Target.Address = "$N$5:$N$32" And Target.Value <> "" Then
Range("O5:O32").Value = Date + Time
End If

i want the target address between n5 until n32 and the range is between o5 until 032

thank you very much sir
Click to expand...
Hi SlamberGamer, welcome to the boards.

I think you just need to change the code you supplied above to the following:

Code: 
If Not Intersect(Target, Range("$N$5:$N$32")) Is Nothing And Target.Value <> "" Then
    Target.Offset(0, 1).Value = Date + Time
End If

Basically it reads as: "If you update a cell in range N5:N32 and the updated value is not blank then update the adjacent cell in column O with the date and time"
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Fishboy said:
Hi SlamberGamer, welcome to the boards.

I think you just need to change the code you supplied above to the following:

Code: 
If Not Intersect(Target, Range("$N$5:$N$32")) Is Nothing And Target.Value <> "" Then
    Target.Offset(0, 1).Value = Date + Time
End If

Basically it reads as: "If you update a cell in range N5:N32 and the updated value is not blank then update the adjacent cell in column O with the date and time"
Click to expand...

thx u very much for the reply sir.. i have tried that but sadlly it doest work for me.. however i already found 2 script that work best for me...

first script:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$5" And Target.Value <> "" Then
Range("O5").Value = Date + Time
End If
If Target.Address = "$N$6" And Target.Value <> "" Then
Range("O6").Value = Date + Time
End If
If Target.Address = "$N$7" And Target.Value <> "" Then
Range("O7").Value = Date + Time
End If
If Target.Address = "$N$8" And Target.Value <> "" Then
Range("O8").Value = Date + Time
End If
If Target.Address = "$N$9" And Target.Value <> "" Then
Range("O10").Value = Date + Time
End If
If Target.Address = "$N$10" And Target.Value <> "" Then
Range("O11").Value = Date + Time
End If
If Target.Address = "$N$11" And Target.Value <> "" Then
Range("O12").Value = Date + Time
End If
If Target.Address = "$N$12" And Target.Value <> "" Then
Range("O13").Value = Date + Time
End If
If Target.Address = "$N$13" And Target.Value <> "" Then
Range("O14").Value = Date + Time
End If
If Target.Address = "$N$14" And Target.Value <> "" Then
Range("O15").Value = Date + Time
End If
If Target.Address = "$N$15" And Target.Value <> "" Then
Range("O16").Value = Date + Time
End If
If Target.Address = "$N$16" And Target.Value <> "" Then
Range("O17").Value = Date + Time
End If
If Target.Address = "$N$17" And Target.Value <> "" Then
Range("O18").Value = Date + Time
End If
If Target.Address = "$N$18" And Target.Value <> "" Then
Range("O19").Value = Date + Time
End If
If Target.Address = "$N$19" And Target.Value <> "" Then
Range("O20").Value = Date + Time
End If
If Target.Address = "$N$20" And Target.Value <> "" Then
Range("O21").Value = Date + Time
End If
If Target.Address = "$N$21" And Target.Value <> "" Then
Range("O22").Value = Date + Time
End If
If Target.Address = "$N$22" And Target.Value <> "" Then
Range("O23").Value = Date + Time
End If
If Target.Address = "$N$23" And Target.Value <> "" Then
Range("O24").Value = Date + Time
End If
If Target.Address = "$N$24" And Target.Value <> "" Then
Range("O25").Value = Date + Time
End If
If Target.Address = "$N$25" And Target.Value <> "" Then
Range("O26").Value = Date + Time
End If
If Target.Address = "$N$26" And Target.Value <> "" Then
Range("O27").Value = Date + Time
End If
If Target.Address = "$N$27" And Target.Value <> "" Then
Range("O28").Value = Date + Time
End If
If Target.Address = "$N$28" And Target.Value <> "" Then
Range("O29").Value = Date + Time
End If
If Target.Address = "$N$29" And Target.Value <> "" Then
Range("O30").Value = Date + Time
End If
If Target.Address = "$N$30" And Target.Value <> "" Then
Range("O31").Value = Date + Time
End If
If Target.Address = "$N$31" And Target.Value <> "" Then
Range("O32").Value = Date + Time
End If
If Target.Address = "$N$32" And Target.Value <> "" Then
Range("O33").Value = Date + Time
End If


End Sub

2nd Script:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("N5:N32")) Is Nothing Then Exit Sub
Range("O" & Target.Row) = Now()
End Sub

After this i realized that this doest work in conditional cell... if can do you know how to make timestamp for conditional.. eg


eg:
=IF((F9="SELL")*AND(H9="GOOD"),"SELL"&"",IF((F9="BUY")*AND(H9="GOOD"),"BUY"))

it will trigger timestamp for either condition meet..
 
Upvote 0
SlamberGamer said:
thx u very much for the reply sir.. i have tried that but sadlly it doest work for me.. however i already found 2 script that work best for me...

first script:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$5" And Target.Value <> "" Then
    Range("O5").Value = Date + Time
End If
If Target.Address = "$N$6" And Target.Value <> "" Then
    Range("O6").Value = Date + Time
End If
If Target.Address = "$N$7" And Target.Value <> "" Then
    Range("O7").Value = Date + Time
End If
If Target.Address = "$N$8" And Target.Value <> "" Then
    Range("O8").Value = Date + Time
End If
If Target.Address = "$N$9" And Target.Value <> "" Then
    Range("O10").Value = Date + Time
End If
If Target.Address = "$N$10" And Target.Value <> "" Then
    Range("O11").Value = Date + Time
End If
If Target.Address = "$N$11" And Target.Value <> "" Then
    Range("O12").Value = Date + Time
End If
If Target.Address = "$N$12" And Target.Value <> "" Then
    Range("O13").Value = Date + Time
End If
If Target.Address = "$N$13" And Target.Value <> "" Then
    Range("O14").Value = Date + Time
End If
If Target.Address = "$N$14" And Target.Value <> "" Then
    Range("O15").Value = Date + Time
End If
If Target.Address = "$N$15" And Target.Value <> "" Then
    Range("O16").Value = Date + Time
End If
If Target.Address = "$N$16" And Target.Value <> "" Then
    Range("O17").Value = Date + Time
End If
If Target.Address = "$N$17" And Target.Value <> "" Then
    Range("O18").Value = Date + Time
End If
If Target.Address = "$N$18" And Target.Value <> "" Then
    Range("O19").Value = Date + Time
End If
If Target.Address = "$N$19" And Target.Value <> "" Then
    Range("O20").Value = Date + Time
End If
If Target.Address = "$N$20" And Target.Value <> "" Then
    Range("O21").Value = Date + Time
End If
If Target.Address = "$N$21" And Target.Value <> "" Then
    Range("O22").Value = Date + Time
End If
If Target.Address = "$N$22" And Target.Value <> "" Then
    Range("O23").Value = Date + Time
End If
If Target.Address = "$N$23" And Target.Value <> "" Then
    Range("O24").Value = Date + Time
End If
If Target.Address = "$N$24" And Target.Value <> "" Then
    Range("O25").Value = Date + Time
End If
If Target.Address = "$N$25" And Target.Value <> "" Then
    Range("O26").Value = Date + Time
End If
If Target.Address = "$N$26" And Target.Value <> "" Then
    Range("O27").Value = Date + Time
End If
If Target.Address = "$N$27" And Target.Value <> "" Then
    Range("O28").Value = Date + Time
End If
If Target.Address = "$N$28" And Target.Value <> "" Then
    Range("O29").Value = Date + Time
End If
If Target.Address = "$N$29" And Target.Value <> "" Then
    Range("O30").Value = Date + Time
End If
If Target.Address = "$N$30" And Target.Value <> "" Then
    Range("O31").Value = Date + Time
End If
If Target.Address = "$N$31" And Target.Value <> "" Then
    Range("O32").Value = Date + Time
End If
If Target.Address = "$N$32" And Target.Value <> "" Then
    Range("O33").Value = Date + Time
End If

End Sub
Click to expand...

Hi again SlamberGamer, I am sorry to hear that my code didn't work for you, which I find strange as I can show it working if you download my test workbook HERE. At the risk of sounding "snobbish" the code above is pretty basic and unnecessarily bloated, and basically is a much longer-winded version of what my code was already doing.

SlamberGamer said:
2nd Script:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N5:N32")) Is Nothing Then Exit Sub
    Range("O" & Target.Row) = Now()
End Sub
Click to expand...
Again the above code here is a slightly less robust version of what my code is doing. It is also worth noting that you couldn't have both this and the first script you shared in the same worksheet module without removing the sub name / end sub lines from one of the scripts, and ensuring that both lots of code are contained within a single sub name / end sub. Furthermore they both do the same thing so one of them would be pointless anyway.

SlamberGamer said:
After this i realized that this doest work in conditional cell... if can do you know how to make timestamp for conditional.. eg


eg:
=IF((F9="SELL")*AND(H9="GOOD"),"SELL"&"",IF((F9="BUY")*AND(H9="GOOD"),"BUY"))

it will trigger timestamp for either condition meet..
Click to expand...
I really don't understand here. Do you mean conditional as in "Conditional Formatting" or conditional as in "Based on an IF statement"? If you mean Conditional Formatting then as the name suggests this feature can change the actual formatting of a cell (font type, font size, colour of cell, borders etc) but cannot update cell values. It is also worth noting that if you try to make a formulaic date stamp solution instead then the formula version of date and time will automatically update to the current date every new calendar day which most likely is not ideal. The solution here would also be using VBA for a more complex Worksheet_Change event macro, but you would need to go into more detail and explain how the IF statement should be working, as your example above is a little all over the place. I assume you meant something like this:

=IF(AND(F9="SELL",H9="GOOD"),"SELL",IF(AND(F9="BUY",H9="GOOD"),"BUY",""))

But even if that is what you meant, I do not understand how this is related to your time stamp.
 
Last edited:
Upvote 0
Fishboy said:
Hi again SlamberGamer, I am sorry to hear that my code didn't work for you, which I find strange as I can show it working if you download my test workbook HERE. At the risk of sounding "snobbish" the code above is pretty basic and unnecessarily bloated, and basically is a much longer-winded version of what my code was already doing.


Again the above code here is a slightly less robust version of what my code is doing. It is also worth noting that you couldn't have both this and the first script you shared in the same worksheet module without removing the sub name / end sub lines from one of the scripts, and ensuring that both lots of code are contained within a single sub name / end sub. Furthermore they both do the same thing so one of them would be pointless anyway.


I really don't understand here. Do you mean conditional as in "Conditional Formatting" or conditional as in "Based on an IF statement"? If you mean Conditional Formatting then as the name suggests this feature can change the actual formatting of a cell (font type, font size, colour of cell, borders etc) but cannot update cell values. It is also worth noting that if you try to make a formulaic date stamp solution instead then the formula version of date and time will automatically update to the current date every new calendar day which most likely is not ideal. The solution here would also be using VBA for a more complex Worksheet_Change event macro, but you would need to go into more detail and explain how the IF statement should be working, as your example above is a little all over the place. I assume you meant something like this:

=IF(AND(F9="SELL",H9="GOOD"),"SELL",IF(AND(F9="BUY",H9="GOOD"),"BUY",""))

But even if that is what you meant, I do not understand how this is related to your time stamp.
Click to expand...

Thank you very much FishBoy for your detailed explanation.. i have tried massive list of script but failed. i want to create timestamp if condition meet "Based on an IF statement". your code work if i manually type the value. but no when the condition change.. eg: from BUY to SELL or from SELL to BUY.
 
Upvote 0
SlamberGamer said:
Thank you very much FishBoy for your detailed explanation.. i have tried massive list of script but failed. i want to create timestamp if condition meet "Based on an IF statement". your code work if i manually type the value. but no when the condition change.. eg: from BUY to SELL or from SELL to BUY.
Click to expand...
Ahh I see. I guess then that changing from BUY to SELL or vice versa is actually the result of a formula. Worksheet_Change events cannot be triggered be changes made by formulas. Instead you would either need to A) use a Worksheet_Calculate event which can be triggered by formula results with the potential downside of the code triggering on any recalculation on the sheet which may end up be resource intensive, or B) looking for the most manual part of the chain to see what manual changes trigger of the chain of events resulting in the formula result changing, then having a Worksheet_Change event triggered by that instead.

Are cells F9 and / or H9 being updated manually to produce the formula result?
 
Upvote 0
Fishboy said:
Ahh I see. I guess then that changing from BUY to SELL or vice versa is actually the result of a formula. Worksheet_Change events cannot be triggered be changes made by formulas. Instead you would either need to A) use a Worksheet_Calculate event which can be triggered by formula results with the potential downside of the code triggering on any recalculation on the sheet which may end up be resource intensive, or B) looking for the most manual part of the chain to see what manual changes trigger of the chain of events resulting in the formula result changing, then having a Worksheet_Change event triggered by that instead.

Are cells F9 and / or H9 being updated manually to produce the formula result?
Click to expand...

thank you sir for fast respond
My excel all run automation by pulling data using dde.. i do not enter manually.. i tried changing the Private Sub Worksheet_Change to Private Sub Worksheet_Calculate. give me error. well i guess that is not how it work..
 
Upvote 0
SlamberGamer said:
thank you sir for fast respond
My excel all run automation by pulling data using dde.. i do not enter manually.. i tried changing the Private Sub Worksheet_Change to Private Sub Worksheet_Calculate. give me error. well i guess that is not how it work..
Click to expand...
Hi again SlamberGamer,

Try replacing the entire code that I have already provided you with the following:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


For Each Cell In Range("N5:N32")
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        If Cell.Offset(0, 1).Value = "" Then
            Cell.Offset(0, 1).Value = Date + Time
        End If
    End If
Next Cell


End Sub
 
Upvote 0
Fishboy said:
Hi again SlamberGamer,

Try replacing the entire code that I have already provided you with the following:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


For Each Cell In Range("N5:N32")
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        If Cell.Offset(0, 1).Value = "" Then
            Cell.Offset(0, 1).Value = Date + Time
        End If
    End If
Next Cell


End Sub
Click to expand...

Thank you very much bigfish !!!! it work.. i know i should't ask more.. but the code doest overwrite when the condition change. if the timestamp already there.. is there anyway you can make it go blank if the condition is not sell or buy.. or at least it will overwrite the old timestamp if the condition change.. thank very much again!! it have been a great help.. search alot forum dont find any solution until you!!
 
Last edited:
Upvote 0
SlamberGamer said:
Thank you very much bigfish !!!! it work.. i know i should't ask more.. but the code doest overwrite when the condition change. if the timestamp already there.. is there anyway you can make it go blank if the condition is not sell or buy.. or at least it will overwrite the old timestamp if the condition change.. thank very much again!! it have been a great help.. search alot forum dont find any solution until you!!
Click to expand...
OK, so assuming that you want the time stamp to overwrite and change EVERY TIME the condition changes to BUY or SELL, or to show no time stamp at all if the condition is NOT met, then try the following code instead:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


For Each Cell In Range("N5:N32")
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        Cell.Offset(0, 1).Value = Date + Time
    Else
        Cell.Offset(0, 1).Value = ""
    End If
Next Cell


End Sub
 
Last edited:
Upvote 0
Fishboy said:
OK, so assuming that you want the time stamp to overwrite and change EVERY TIME the condition changes to BUY or SELL, or to show no time stamp at all if the condition is NOT met, then try the following code instead:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


For Each Cell In Range("N5:N32")
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        Cell.Offset(0, 1).Value = Date + Time
    Else
        Cell.Offset(0, 1).Value = ""
    End If
Next Cell


End Sub
Click to expand...

that one doest work it will overwrite everthing and i end up get the current date and time. can u teach me how to put multiple condition

eg: i tried this but doest work

Private Sub Worksheet_Calculate()
Dim Cell As Range




For Each Cell In Range("N5:N32")
If Cell.Value = "BUY"
Cell.Offset(0, 1).Value = Date + Time
Else
If Cell.Value = "SELL"
Cell.Offset(0, 1).Value = Date + Time
Else
If Cell.Value = ""
Cell.Offset(0, 1).Value = ""
End If
Next Cell


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Insert new row with incremental formula
Replies
8
Views
479
Sheik
S
K
  • Question Question
Time and date stamp with drop down menu
Replies
0
Views
395
kmsprague
K
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
387
maiwarits
M
R
  • Question Question
change time stamp to 15 minute intervals
Replies
2
Views
1K
Cubist
Cubist
C
  • Solved
Count number of entries that have a date stamp of last week
Replies
6
Views
633
Joe4
Joe4

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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