Hi @chrisnoonyes and @Marix
You may find some insight on this post;
The ultimate conclusion being that a sheet will not automatically recalculate unless it is forced to by a function that demands it. In this case, the random number generator could act as a catalyst for recalculation.
On the matter of cell values, I was mystified by the value of “jjjj-02”. It was mentioned that the formula TEXT(A2,“yyyy-MM”) was used but “jjjj-02” was the result.
I dug very deep, and found the following in the sample spreadsheet. (this is from the XML data that is the building blocks of the sheet)
<c r="C2" t="str"><f t="shared" ref="C2:C27" si="0">TEXT(B2,"jjjj-MM")</f><v>jjjj-11</v></c>
What it says is Cell C2 has a formula of TEXT(B2,“jjjj-MM”) and a value of “jjjj-11”.
This seems to be indicative of why fields have “j” characters instead of the year value. I could find no reference to “j” being a date format rule character, unless Marix has some knowledge of one.
But the cells have values, and with no catalyst, as mentioned previously, no recalculation on open will occur, and it would appear, that the value of “jjjj-11” is a valid result of the formula.
I hope this may provide a little insight to your issue.