Excel sheets TextJoin activating when editing unrelated cells

Question

Just as the title suggests, I am using a texjoin in a larger function in order to keep a running history of sorts. The issue is that when I edit cells unrelated to the formula, the texjoin feature activates creating duplicates. I am not against using vba code however I would like to keep my functions concise.

The formula in question.(home cell is B1) =IF(NOT($A1=""),TEXTJOIN("",TRUE,$B1," ",$A1,"*",TEXT($P$2,"mm/dd/yyy")," ",$R$1), IF($B1=0," ", " "))

Thanks for any advice!

EDIT: an explanation of what this is trying to do. Basically if there is something in a1 then attempt to concatenate that with what is already in b1 and add the current date and the username initials(referenced from a table for ease)

Apparently the date function is volatile. I'm assuming using vba script to get a username is also volatile. Does that influence the issues I'm experiencing?

Answer 1

TLDR: formulas aren't going to work for you here. Switch to VBA using a Worksheet_Change event.

... TextJoin activating when editing unrelated cells. No, it's not.

It activating (aka calculating) because it's part of a formula that includes a reference to a cell containing a Volatile formula. Excel regards a Volatile function as changed every time a sheet calculates. So every cell that contains a Volatile function, or a reference to a cell containing a Volatile function (recursively) is calculated.

Re VBA functions (UDF's), they not Volatile by default ( they can be made Volatile by using Application.Volitile )