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:
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
Click to expand...

and it posible for me to chose multiple range? eg if i want to chose M5:M32 and L5:L32
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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...

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...
Click to expand...

SlamberGamer said:
that one doest work it will overwrite everthing
Click to expand...

Is that not specifically what you asked for?

To clarify just to prevent us going round in circles...

- If the value changes to BUY then update the adjacent cell with a NEW time stamp?
- If the value changes to SELL then update the adjacent cell with a NEW time stamp?
- If the value changes to blank then clear the time stamp from the adjacent cell?
- If the value changes to anything else then clear the time stamp from the adjacent cell?

Can you let me know which of the above statements are true? All of them? Some of them? If so, which ones?

SlamberGamer said:
and it posible for me to chose multiple range? eg if i want to chose M5:M32 and L5:L32
Click to expand...
Do you mean as well as N:O or instead of N:O?
 
Last edited:
Upvote 0
Fishboy said:
Is that not specifically what you asked for?

To clarify just to prevent us going round in circles...

- If the value changes to BUY then update the adjacent cell with a NEW time stamp?
- If the value changes to SELL then update the adjacent cell with a NEW time stamp?
- If the value changes to blank then clear the time stamp from the adjacent cell?
- If the value changes to anything else then clear the time stamp from the adjacent cell?

Can you let me know which of the above statements are true? All of them? Some of them? If so, which ones?


Do you mean as well as N:O or instead of N:O?
Click to expand...

All of them..

And i mean as well as N:O

EG: that is my sheet i want to update timestamp for column L and N.

Sorry FishBoy for all the trouble! forgive me. and thank you very much for all your help and keep it up with my request! u r the best sir.

578ffd8a6fc148b3aec120d9c34e8f68.png
 
Upvote 0
SlamberGamer said:
All of them..
Click to expand...
That is exactly what it is already doing...

SlamberGamer said:
And i mean as well as N:O

EG: that is my sheet i want to update timestamp for column L and N.

Sorry FishBoy for all the trouble! forgive me. and thank you very much for all your help and keep it up with my request! u r the best sir.
Click to expand...
This should check both columns L and N and update columns M and O respectively:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


' For each cell in the specified N column range
For Each Cell In Range("N5:N32")
    ' If the cell value is BUY or SELL then...
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        ' Update the adjacent cell with the date and time
        Cell.Offset(0, 1).Value = Date + Time
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    Else
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


' For each cell in the specified L column range
For Each Cell In Range("L5:L32")
    ' If the cell value is BUY or SELL then...
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        ' Update the adjacent cell with the date and time
        Cell.Offset(0, 1).Value = Date + Time
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    Else
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


End Sub
 
Upvote 0
Fishboy said:
That is exactly what it is already doing...


This should check both columns L and N and update columns M and O respectively:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


' For each cell in the specified N column range
For Each Cell In Range("N5:N32")
    ' If the cell value is BUY or SELL then...
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        ' Update the adjacent cell with the date and time
        Cell.Offset(0, 1).Value = Date + Time
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    Else
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


' For each cell in the specified L column range
For Each Cell In Range("L5:L32")
    ' If the cell value is BUY or SELL then...
    If Cell.Value = "BUY" Or Cell.Value = "SELL" Then
        ' Update the adjacent cell with the date and time
        Cell.Offset(0, 1).Value = Date + Time
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    Else
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


End Sub
Click to expand...

Thank you very much fishboy... but like i told before. the clear command will repetive clear the cell and end up giving me current time .. eg sample as shown

9991fc210e9c432c9057cd1107022a14.png
 
Upvote 0
SlamberGamer said:
Thank you very much fishboy... but like i told before. the clear command will repetive clear the cell and end up giving me current time .. eg sample as shown

9991fc210e9c432c9057cd1107022a14.png
Click to expand...
Right, so it seems that the code needs to be able to differentiate between whether or not the existing time stamp is the result of the current cell value or the previous cell value. With this in mind I have taken the liberty of adding (BUY) or (SELL) to the end of the time stamp. This was necessary so that if a previous cell value was BUY the last time the code ran, and is still BUY on the next time the code runs, the time stamp does not get overwritten.

Try out the following adaptation:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


' For each cell in the specified N column range
For Each Cell In Range("N5:N32")
    ' If the cell value is BUY then...
    If Cell.Value = "BUY" Then
        ' If the adjacent cell does not end with Y) then...
        If Right(Cell.Offset(0, 1), 2) <> "Y)" Then
            ' Update the adjacent cell with the date, time and (BUY)
            Cell.Offset(0, 1).Value = Date + Time & " (BUY)"
        End If
    ' Else if the cell value is SELL then...
    ElseIf Cell.Value = "SELL" Then
        ' If the adjacent cell does not end with L) then...
        If Right(Cell.Offset(0, 1).Value, 2) <> "L)" Then
            ' Update the adjacent cell with the date, time and (SELL)
            Cell.Offset(0, 1).Value = Date + Time & " (SELL)"
        End If
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    ElseIf Cell.Value <> "BUY" & Cell.Value <> "SELL" Then
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


' For each cell in the specified L column range
For Each Cell In Range("L5:L32")
    ' If the cell value is BUY then...
    If Cell.Value = "BUY" Then
        ' If the adjacent cell does not end with Y) meaning the previous value was SELL then...
        If Right(Cell.Offset(0, 1), 2) <> "Y)" Then
            ' Update the adjacent cell with the date, time and (BUY)
            Cell.Offset(0, 1).Value = Date + Time & " (BUY)"
        End If
    ' Else if the cell value is SELL then...
    ElseIf Cell.Value = "SELL" Then
        ' If the adjacent cell does not end with L) meaning the previous value was BUY then...
        If Right(Cell.Offset(0, 1).Value, 2) <> "L)" Then
            ' Update the adjacent cell with the date, time and (SELL)
            Cell.Offset(0, 1).Value = Date + Time & " (SELL)"
        End If
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    ElseIf Cell.Value <> "BUY" & Cell.Value <> "SELL" Then
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


End Sub
 
Upvote 0
Fishboy said:
Right, so it seems that the code needs to be able to differentiate between whether or not the existing time stamp is the result of the current cell value or the previous cell value. With this in mind I have taken the liberty of adding (BUY) or (SELL) to the end of the time stamp. This was necessary so that if a previous cell value was BUY the last time the code ran, and is still BUY on the next time the code runs, the time stamp does not get overwritten.

Try out the following adaptation:

Code: 
Private Sub Worksheet_Calculate()
Dim Cell As Range


' For each cell in the specified N column range
For Each Cell In Range("N5:N32")
    ' If the cell value is BUY then...
    If Cell.Value = "BUY" Then
        ' If the adjacent cell does not end with Y) then...
        If Right(Cell.Offset(0, 1), 2) <> "Y)" Then
            ' Update the adjacent cell with the date, time and (BUY)
            Cell.Offset(0, 1).Value = Date + Time & " (BUY)"
        End If
    ' Else if the cell value is SELL then...
    ElseIf Cell.Value = "SELL" Then
        ' If the adjacent cell does not end with L) then...
        If Right(Cell.Offset(0, 1).Value, 2) <> "L)" Then
            ' Update the adjacent cell with the date, time and (SELL)
            Cell.Offset(0, 1).Value = Date + Time & " (SELL)"
        End If
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    ElseIf Cell.Value <> "BUY" & Cell.Value <> "SELL" Then
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


' For each cell in the specified L column range
For Each Cell In Range("L5:L32")
    ' If the cell value is BUY then...
    If Cell.Value = "BUY" Then
        ' If the adjacent cell does not end with Y) meaning the previous value was SELL then...
        If Right(Cell.Offset(0, 1), 2) <> "Y)" Then
            ' Update the adjacent cell with the date, time and (BUY)
            Cell.Offset(0, 1).Value = Date + Time & " (BUY)"
        End If
    ' Else if the cell value is SELL then...
    ElseIf Cell.Value = "SELL" Then
        ' If the adjacent cell does not end with L) meaning the previous value was BUY then...
        If Right(Cell.Offset(0, 1).Value, 2) <> "L)" Then
            ' Update the adjacent cell with the date, time and (SELL)
            Cell.Offset(0, 1).Value = Date + Time & " (SELL)"
        End If
    ' Else if the cell value is ANYTHING other than BUY or SELL (including blank) then...
    ElseIf Cell.Value <> "BUY" & Cell.Value <> "SELL" Then
        ' Clear the adjacent cell
        Cell.Offset(0, 1).Value = ""
    End If
' Check next cell in specified range
Next Cell


End Sub
Click to expand...

WOW.. finally! thx you very much sir!! u are awesome.. finally it work! i have trying very hard to understand your code.. but this one is a mind blown.. i only understand half of it !.. thank you very much sir.. !!!! just a liltle question where did you learn all this?? thinking of learning some so i can help someone in need in future like you did to me!! thank you again sir!!
 
Upvote 0
SlamberGamer said:
WOW.. finally! thx you very much sir!! u are awesome.. finally it work! i have trying very hard to understand your code.. but this one is a mind blown.. i only understand half of it !.. thank you very much sir.. !!!! just a liltle question where did you learn all this?? thinking of learning some so i can help someone in need in future like you did to me!! thank you again sir!!
Click to expand...
Happy to help, glad to hear we got there in the end.

As for where did i learn all this? Mostly right here on these forums. I started out coming here looking for help but after a while I had picked up enough knowledge to start helping others. In all honesty I am totally still learning right now, and helping other people exposes me to situations and problems that I might not have otherwise encountered in my day to day work. These forums have been invaluable to me, and helping others helps me to keep learning. It's a win-win situation! ;)
 
Upvote 0
Fishboy said:
Happy to help, glad to hear we got there in the end.

As for where did i learn all this? Mostly right here on these forums. I started out coming here looking for help but after a while I had picked up enough knowledge to start helping others. In all honesty I am totally still learning right now, and helping other people exposes me to situations and problems that I might not have otherwise encountered in my day to day work. These forums have been invaluable to me, and helping others helps me to keep learning. It's a win-win situation! ;)
Click to expand...

i see.. i will take note for your experience.. thx fishboy it have been a GREAT HELP. !! i hope you will continue to help other in these forum!. big salute for you sir! :bow:
 
Upvote 0
Hi help pls

my timestamp give an error #n/a when i reopened the excel file



=if(ab8<>"", if(u8="",now(),u8), "")
 
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