Time Stamp Based on Conditional IF statement

[mduberst]

Hi All,

I am trying to write a function that creates a timestamp when a certain word is entered in a specific cell. The problem is, the value of the cell that the timestamp is based on is constantly changing. I want the timestamp to record the date the first time the formula is found true, and then keep that result, no matter what else happens in the cell the formula is triggered by.

here is the formula i have worked up so far: =IF(B1="quoted_2",IF(A1="",TODAY(),A1),"")

i turned on iterations and set it to 1. below is an illustration of the desired result.

ACTUAL DATE	STATUS	DATE QUOTED
12/11/16	Received_1
12/13/16	Quoted_2	12/13/2016
12/17/16	Approved_3	12/13/2016

<tbody>
</tbody>

 

[Joe4]

You will need VBA to do that.
Are you trying to put this value in column A or C?
How does the other date column come into play, if at all?

 

[mduberst]

sorry, i guess my example sort of muddled things. the actual date column was just to illustrate how i need the date quoted to remain constant once the actual date changes. the formula i wrote was on a mock sheet, i figured i'd keep it simple and try to get it to work in a1, then just change the names of the cells as needed. i have looked in to trying to do this with VBA but i dont really know anything about it.

 

[Joe4]

There is special VBA code call Event Procedures, which is VBA code that runs automatically upon some event happening, like the update of certain cells. We can create code that will run whenever column B is updated with a certain value, and column A is blank.

The code has to be written a certain way, and needs to be put in a specific VBA module. There is a good write-up on event procedures here: Events In Excel VBA

So, right-click on the sheet tab name at the bottom of your sheet, select View Code, and paste the following code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    Dim cell As Range

'   Check to see if column B updated (if not, exit)
    Set myRange = Intersect(Target, Range("B:B"))
    If myRange Is Nothing Then Exit Sub
    
'   Loop through changed cells in column B
    For Each cell In myRange
'       Check value in column B and check to see if column A is blank
        If LCase(cell) = "quoted_2" And cell.Offset(0, -1) = "" Then
            cell.Offset(0, -1) = Date
        End If
    Next cell
    
End Sub

Now go ahead and try it out by adding values in column B. It will automatically update column A with a hard-coded datestamp if you enter "quoted_2" in column B and column A is blank.

 

[mduberst]

thanks! it works! awesome. any chance you know javascript well enough to translate this to javascript for me? i want to use this in google sheets

 

[Joe4]

any chance you know javascript well enough to translate this to javascript for me? i want to use this in google sheets

Sorry, but I don't. Just starting to learn JavaScript.