excel-formulaevaluationweek-numberexcel-2021

Formula evaluation returns weird value

I created a formula in excel to get the iso.weeknumber in 2 numbers:

=JAAR(A2)&" "&ALS(LENGTE(ISO.WEEKNUMMER(A2)=1);"0"&ISO.WEEKNUMMER(A2);ISO.WEEKNUMMER(A2))

The result is this:

enter image description here

somehow, Excel thinks week 10 is length 1 in this formula. I checked, and only the length of weeknumber of the used date is 2 (see value in column N).

I then started formula evaluation and that is where something weird happens: enter image description here

enter image description here

the result is false. but then this happens:

enter image description here

where does that 6 come from?

Solution

  • I created the formula in a new unformatted column, and then it works as it should:

    =JAAR(A2)&" "&ALS(LENGTE(ISO.WEEKNUMMER(A2))<2;0&ISO.WEEKNUMMER(A2);ISO.WEEKNUMMER(A2))