If a cell contains the word "Completed", then return the date/time when it changed.

[awkwardjae]

Hi I have a excel form (mostly use it through sharepoint) where people submit a google form and request tasks for my team to complete. This google form autofills a row each time a request is submitted with different information not limited to submission date, ID, Name, Request etc. We have a status column (column S) in excel that contains one of "Not Started" default when request submitted, "In Progress" when someone enters their initial in "Prep" Column, "Not Tested" which we manually change if no do the request and "Completed" when we sign our initial in the "Done" Column AND "Prep" Column. All of this are automatically triggered (with exception of Not Tested). What I would like to include is a seperate column due to demand and audit reasons that says "Completed On" (column T) which essentially gets updated with the date and time that the Status column says "Completed".
Few things to note, would like this to apply automatically as requests are entered and status changes.
Currently we are on row 2751
If it cannot be applied retroactively that is fine, just for future requests
If possible if the status changes from Completed to another (due to someone accidentally signing) the completed on becomes blank.

I have tried:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if any updates to specified range
Set rng = Intersect(Range("S2:S150000"), Target)

' Exit sub if no changes to watched range
If rng Is Nothing Then Exit Sub

' Loop through changed cells
Application.EnableEvents = False
For Each cell In rng
' See if cell is set to "Completed"
If cell.Value = "Completed" Then
' Add date stamp to column T of same row
cell.Offset(0, 1).Value = Now
End If
Next cell
Application.EnableEvents = True

End Sub

But does not seem to trigger the date showing.
Any help, advice, direction is appreciated. Thank you

 

[dreid1011]

Upon testing, this seems to work as expected. Do you have any other VBA code in the workbook? Either way, here is the code with the update to clear the cell if col S does not equal "Completed".

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if any updates to specified range
Set rng = Intersect(Range("S2:S150000"), Target)

' Exit sub if no changes to watched range
If rng Is Nothing Then Exit Sub

' Loop through changed cells
Application.EnableEvents = False
For Each cell In rng
    ' See if cell is set to "Completed"
    If cell.Value = "Completed" Then
        ' Add date stamp to column T of same row
        cell.Offset(0, 1).Value = Now
    Else
        cell.Offset(0, 1).Value = ""
    End If
Next cell
Application.EnableEvents = True

End Sub

 

[awkwardjae]

Upon testing, this seems to work as expected. Do you have any other VBA code in the workbook? Either way, here is the code with the update to clear the cell if col S does not equal "Completed".

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

' See if any updates to specified range
Set rng = Intersect(Range("S2:S150000"), Target)

' Exit sub if no changes to watched range
If rng Is Nothing Then Exit Sub

' Loop through changed cells
Application.EnableEvents = False
For Each cell In rng
    ' See if cell is set to "Completed"
    If cell.Value = "Completed" Then
        ' Add date stamp to column T of same row
        cell.Offset(0, 1).Value = Now
    Else
        cell.Offset(0, 1).Value = ""
    End If
Next cell
Application.EnableEvents = True

End Sub

Thanks for the insight!
Turns out the issue I was having was that the S column isn't the word "Completed" but actually the nested IF Statement that reads..
=IF(OR(M2>0,P2>0),IF(AND(O2>0,R2>0),"Completed","In Progress"),"Not Started")

How would this change the code, because having trouble working with IF AND/OR in Vba. Thank you!

 

[dreid1011]

Thanks for the insight!
Turns out the issue I was having was that the S column isn't the word "Completed" but actually the nested IF Statement that reads..
=IF(OR(M2>0,P2>0),IF(AND(O2>0,R2>0),"Completed","In Progress"),"Not Started")

How would this change the code, because having trouble working with IF AND/OR in Vba. Thank you!

I don't see how that changes the code looking at column S for the word "completed". It should find it even as a formula result instead of a manually entered value.

Are you having issues with the code still?

 

[awkwardjae]

I don't see how that changes the code looking at column S for the word "completed". It should find it even as a formula result instead of a manually entered value.

Are you having issues with the code still?

Hi, the code works when I manually type "Completed", but when the S column gets triggered by the IF statement to Completed it doesn't do it. So I'm not sure what the issue is.

 

[awkwardjae]

Hi, the code works when I manually type "Completed", but when the S column gets triggered by the IF statement to Completed it doesn't do it. So I'm not sure what the issue is.

So that's why I was wondering if setting it to the IF condition for completed as opposed to the Value completed would've made a difference.

 

[Skyybot]

Maybe try

.Value2

in place of all of the

.Value

?

 

[dreid1011]

Hi, the code works when I manually type "Completed", but when the S column gets triggered by the IF statement to Completed it doesn't do it. So I'm not sure what the issue is.

Oh, I think I see. The formula changing the value does not trigger the Worksheet_Change event. How do the values in columns M, O, P and R change? Are those formulas also, or manual entry?

 

[awkwardjae]

Oh, I think I see. The formula changing the value does not trigger the Worksheet_Change event. How do the values in columns M, O, P and R change? Are those formulas also, or manual entry?

Oh, that probably is it. So they are all manually entered. M and P you put your initial when you start task 1 and 2 respectively, O and R when you complete them. So once you start a task it becomes in progress, and when you finish both it's completed.

 

[dreid1011]

Then you are just checking to see if those columns are not blank?