Timestamp Help

[JPhotonics]

I looked through the forum already and found some helpful information, but not quite what I need. Here is the link I found helpful - http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/

What I am trying to accomplish is to display a timestamp when a cell/text in my worksheet changes to a certain text output.

This specific cell's default text is "Pending Review" (Cell C7). The text in this cell will change based on the answers to a set of questions. The status changes to either "Approved", "Approved w/ Comments" or "Rejected" based on a formula. What I would like to do, is to display the timestamp date when the cell changes to become either "Approved" or "Approved w/ Comments". I DO NOT want to set/change the timestamp if the cell changes to say "Rejected".

After displaying the timestamp on this worksheet, I'd then like to also display that timestamp date on my "cover" page in a specific cell. Can I accomplish that by simply having the cell on my cover page call out the same cell from the other worksheet? (Ex. =D7)

I have attached my worksheet to this post. Cells B16 & B17 are where the questions are answered which determines the Status displayed in cell D11 as well as the Overall Status in displayed in cell C7.

 

[vletm]

... something like this?
Cell C7 : Could You use D11:D20 instead D:D
Cell D7 : =IF(OR(C7="Approved";C7="Approved w/ Comments");NOW();"")
"Cover": normal link from cell D7

 

[JPhotonics]

... something like this?
Cell C7 : Could You use D11:D20 instead D:D
Cell D7 : =IF(OR(C7="Approved";C7="Approved w/ Comments");NOW();"")
"Cover": normal link from cell D7

I don't see why I couldn't use a range of D cells (D11:D20) rather than D:D. The reason it is written as D:D is because there are multiple worksheets varying in length/# of questions.

^^ I just went into the spreadsheet and changed "D:D" to "D11:D20" - the functionality remains the same for that cell. Great news.

I apologize, I do not understand what you mean by your last statement:
"Cover": normal link from Cell D7.

The formula you provided above appears to work. However, if I go and open the document tomorrow, will that timestamp be reset? I need it to remain static to take a snapshot in time of when it was "Approved" or "Approved w/ Comments".

Thank you very much for your quick response and for your help.

 

[narunfca]

Hi!

Press Alt+F11 TO Activate VBA.
Double click on Production Sheet and paste the following VBA code

with best regards
Arun N

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$7" And Range("$D$7").Text = "" Then
  If Range("C7").Text = "Approved" Or Range("C7").Text = "Approved w/ Comments" Then

    Range("D7").FormulaR1C1 = "=NOW()"
    Range("D7").Copy
    Range("D7").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False

  End If
End If

End Sub

 

[JPhotonics]

Hi!

Press Alt+F11 TO Activate VBA.
Double click on Production Sheet and paste the following VBA code

with best regards
Arun N

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$7" And Range("$D$7").Text = "" Then
  If Range("C7").Text = "Approved" Or Range("C7").Text = "Approved w/ Comments" Then

    Range("D7").FormulaR1C1 = "=NOW()"
    Range("D7").Copy
    Range("D7").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False

  End If
End If

End Sub

Thank you for your response.

I have no experience using VBA, so I was hoping to accomplish this using Excel formulas. Also, I am worried about other users being able to open this document and use it in the same way I am able to use it.

If I perform this task using VBA, are all other users who wish to open and use this worksheet required to also activate/use VBA? This document will serve as a template to be used for multiple projects.

 

[narunfca]

Thank you for your response.

I have no experience using VBA, so I was hoping to accomplish this using Excel formulas. Also, I am worried about other users being able to open this document and use it in the same way I am able to use it.

If I perform this task using VBA, are all other users who wish to open and use this worksheet required to also activate/use VBA? This document will serve as a template to be used for multiple projects.

Your requirement of remaining static to take a snapshot in time of when it was "Approved" or "Approved w/ Comments" requires formula to be converted to values once the status of D7 is updated, since now() function will update every time there is change in any cell in that workbook. This has to be done manually by the user. If you want to automate, Coding is the only option.

 

[vletm]

... something like this?
1) Cell C7 : ... of course "D20" and be more too
2) Cell D7 : If without VBA then ... check file
I changed D7 formula and ! add one CheckBox.
If checked then D7 should be locked = no update time.
I didn't test all possibilities ...
with VBA ... replace those 4 rows within 2nd If ... Endif Range("D7") = now
3) "Cover": normal link from cell D7 ... as You wrote:
After displaying the timestamp on this worksheet, I'd then like to also display that timestamp date on my "cover" page in a specific cell. Can I accomplish that by simply having the cell on my cover page call out the same cell from the other worksheet? (Ex. =D7)

 

[JPhotonics]

Your requirement of remaining static to take a snapshot in time of when it was "Approved" or "Approved w/ Comments" requires formula to be converted to values once the status of D7 is updated, since now() function will update every time there is change in any cell in that workbook. This has to be done manually by the user. If you want to automate, Coding is the only option.

Oh okay. I'd like to do it without creating a button like vletm suggested.

If I automate and use the coding, will anyone who has Excel be able to use and run this spreadsheet without doing anything additional? Or would everyone need to activate VBA or do something else to allow it to work properly? I would like all users who have access to Excel to be able to open and use this document and the easier it is, the better because if it is too involved, getting buy-in from others may be tough.

 

[JPhotonics]

Hi!

Press Alt+F11 TO Activate VBA.
Double click on Production Sheet and paste the following VBA code

with best regards
Arun N

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$7" And Range("$D$7").Text = "" Then
  If Range("C7").Text = "Approved" Or Range("C7").Text = "Approved w/ Comments" Then

    Range("D7").FormulaR1C1 = "=NOW()"
    Range("D7").Copy
    Range("D7").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False

  End If
End If

End Sub

Your requirement of remaining static to take a snapshot in time of when it was "Approved" or "Approved w/ Comments" requires formula to be converted to values once the status of D7 is updated, since now() function will update every time there is change in any cell in that workbook. This has to be done manually by the user. If you want to automate, Coding is the only option.

Alright, so I put this code into VBA and it appears to work and creates a timestamp for when "Approved" or "Approved w/ Comments" is shown in Cell C7.

However, if I go in and switch it to be "Rejected" in C7, the timestamp stays with the original time/date of when "Approved" or "Approved w/ Comments" was shown. I'd like the timestamp to go away if "Rejected" is shown in C7 and then re-timestamp if/when "Approved" or "Approved w/ Comments" is shown in C7 again. How can I do this?

I probably should have asked that originally, my apologies.

 

[vletm]

If You cannot or won't use 'code' then
use #2 Reply formulas and
'just set that file to READ_ONLY' manually after it 'ready'!

 

[JPhotonics]

If You cannot or won't use 'code' then
use #2 Reply formulas and
'just set that file to READ_ONLY' manually after it 'ready'!

That is not an option for this file. There will are four (4) sheets within the main spreadsheet - 1 for each phase. Each phase will need to be "Approved" before moving onto the next phase, so I cannot switch it to read-only once something becomes approved.

If I need to use VBA, I will. I have taken narunfca's code and am using that currently. However, as I posted above, it does not solve all of my issues.

As I posted above:
If I go in and switch it to be "Rejected" in C7, the timestamp stays with the original time/date of when "Approved" or "Approved w/ Comments" was shown. I'd like the timestamp to go away if "Rejected" is shown in C7 and then re-timestamp if/when "Approved" or "Approved w/ Comments" is shown in C7 again. How can I do this?

 

[vletm]

How about this?

 

[narunfca]

Oh okay. I'd like to do it without creating a button like vletm suggested.

If I automate and use the coding, will anyone who has Excel be able to use and run this spreadsheet without doing anything additional? Or would everyone need to activate VBA or do something else to allow it to work properly? I would like all users who have access to Excel to be able to open and use this document and the easier it is, the better because if it is too involved, getting buy-in from others may be tough.

They have to just enable the macro, if asked for. No need to do anything additional.

Revised code given below as per your new requirement.

You have to just refer =Production!D7 in required cell in cover page to display time.

with best regards

Arun N

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$D$7" Then
If Range("C7").Text = "Approved" Or Range("C7").Text = "Approved w/ Comments" Then
If Range("$D$7").Text = "" Then
Range("D7").FormulaR1C1 = "=NOW()"
Range("D7").Copy
Range("D7").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End If
Else
Range("D7").FormulaR1C1 = ""
End If
End If

End Sub