CODEWITHSUNDEEP
exceldatetimeexcel-formulacell
adrisiadrice
adrisiadrice

Reputation: 1

How to set the datetime into an excel cell only one time based on another cell value?

I want to set into one column (for example 'J') the datetime now() but only 1 time, to save the moment that the column (for example 'F') has set the value 'Yes'.

I tried macros but it's not a possibility because I want to share it on TEAMS and it's not working properly. I made this into my desktop app this macro and works fine but I need something similar to this but into the cell functions...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
    Application.EnableEvents = False
If Target.Cells.Column = 6 Then
        n = Target.Row
        If Me.Range("F" & n).Value = "Yes" Then
            Me.Range("J" & n).Value = Format(Now, "DD/HH/MM hh:mm:ss")
        Else: Me.Range("J" & n).Value = ""
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

Instead, I tried a cell with this function but it updates the datetime all the time, and I want to save only the moment that it changes to 'Yes':

=IF(F7="Yes";NOW();"")

I tried this one too but it has an error if I try to use my own cell where the function is set:

=IF(AND(F7="Yes";ISBLANK(J7));NOW();"")

Upvotes: 0

Views: 395

Answers (1)

Bryan Rock
Bryan Rock

Reputation: 632

The problem with using a formula is that it's going to reevaluate all the timestamps. If you really have to use formulas I guess you can try setting the calculation options to manual. It's just risky because the moment you hit F9 you're going to lose all that data.

What about using the new Office Scripts instead? It's supposed to be similar to VBA and work in the browser.

https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel

Upvotes: 1

Related Questions