Static Timestamp

[deftincu]

Hi All,

I’m trying to figure out a formula I can use that will create a timestamp (format of mm/dd/yyy hh am/pm) based off specific selections chosen from a drop down menu in another cell. I’m looking to copy this formula into multiple cells within that same column. I have a couple of conditions it needs to meet:

1) drop down selection that should trigger the timestamp does not have a single name to it, it begins with the main status and then has names after it. For example; “On Hold - John”, “On Hold - Karen”. The common theme is the “On Hold” portion of the text. When any status is chosen that has “On Hold” in it, the timestamp should appear. The other constraint is that this drop down is shared with several other statuses, so I can’t simply use a formula that checks if the column is blank or non-blank.

2) the status drop down will change at some point, but I need that timestamp to remain intact after the status changes from “On Hold..” to the new status.

The first condition is what has left me stumped. If it was as simple as the cell being changed from blank to non-blank I could use the following formula: =IF(AB2<>””,IF(AE2=“”,NOW(),AE2),””)

Another constraint is that this is a shared spreadsheet amongst several people that have various levels of excel skills, ranging from novice to intermediate, so I’d like to keep this as a formula vs VBA if at all possible (if that is not possible, I’m open to code that automates this process and doesn’t require manual intervention). The assistance with this will be greatly appreciated!

 

[rlv01]

Perhaps someone else does, but I don't know how to do it without using VBA.

Book1
	A	B	C	D	E
1	Status	Time Stamp			List
2	On Hold - Karen	07-21-2024 16:07:50			On Hold - John
3	Complete				On Hold - Karen
4	On Hold - John	07-21-2024 16:07:55			On Hold - Latimer
5	Active	07-21-2024 16:08:01			Active
6					Complete
7
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A7	List	=$E$2:$E$6

'
'Located in worksheet code module
'
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Range("A2:A7")) Is Nothing Then
        Dim R As Range

        For Each R In Target
            If InStr(R.Value, "On Hold") > 0 Then
                With R.Offset(0, 1)
                    'Apply timestamp
                    .Value = Format(Date + Time, "mm-dd-yyyy hh:mm:ss")
                    .NumberFormat = "mm-dd-yyyy hh:mm:ss"
                End With
            Else
                'code for other status messages goes here.
                Select Case R.Value
                Case "Active"
                    'do nothing
                Case "Complete"
                    'Clear timestamp
                    R.Offset(0, 1) = ""
                Case Else
                End Select
            End If
        Next R
    End If
End Sub

 

[deftincu]

Perhaps someone else does, but I don't know how to do it without using VBA.

Book1
	A	B	C	D	E
1	Status	Time Stamp			List
2	On Hold - Karen	07-21-2024 16:07:50			On Hold - John
3	Complete				On Hold - Karen
4	On Hold - John	07-21-2024 16:07:55			On Hold - Latimer
5	Active	07-21-2024 16:08:01			Active
6					Complete
7
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:A7	List	=$E$2:$E$6

'
'Located in worksheet code module
'
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Me.Range("A2:A7")) Is Nothing Then
        Dim R As Range

        For Each R In Target
            If InStr(R.Value, "On Hold") > 0 Then
                With R.Offset(0, 1)
                    'Apply timestamp
                    .Value = Format(Date + Time, "mm-dd-yyyy hh:mm:ss")
                    .NumberFormat = "mm-dd-yyyy hh:mm:ss"
                End With
            Else
                'code for other status messages goes here.
                Select Case R.Value
                Case "Active"
                    'do nothing
                Case "Complete"
                    'Clear timestamp
                    R.Offset(0, 1) = ""
                Case Else
                End Select
            End If
        Next R
    End If
End Sub

Thank you for the response. Unfortunately, I discovered that the shared spreadsheet, used online, does not support VBA.

 

[deftincu]

I did see a response from @plettieri, on Friday, with a non VBA solution; however, the message is no longer displayed. I apologize for not responding back to confirm I saw your message and was going to test it out today. Are you able to repost it?

 

[Fluff]

The reply by plettieri was created by one of the AI tools & so was removed. AI is unreliable where Xl is concerned.
For any formula to work you would need to enable iterative calculation & I'm not sure you can do that with online browser version of Excel.