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:
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:
the result is false. but then this happens:
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))
- How to assign a category based on a number range?
- Excel Latest Date Summary
- Find particular word in excel and fetch values
- get respective column value without using vba
- Conditional formatting formula based on value in other sheet
- Excel to dynamically generate SQL script using formula
- Highlight cell if any value matches another column
- Create custom cell reference/array in formula without enclosing string parameters in quotation marks
- Lookup matching cell in a table and return the value of the first cell that's merged upwards to the matching cell
- Create Sequence in Excel with repeating numbers every nth time
- Using average, offset & match together
- First the nth value in a table with a specific condition in Excel
- How to make Excel "IF" formula calculate blank cells as "0"?
- Can I have an ordered list with both numbers and letters in it?
- Length of strings with Unicode in Excel
- REPT function in Excel
- In Excel how to find days from dates in a specific cell date minus date in same row column without specified cell
- TEXTSPLIT combined with BYROW returns an unexpected result when using an array of strings as input
- An advanced formula for getting serial nos automatically in excel
- How to convert a table to a list with row & column header
- Calculate SUMPRODUCT with multiple column criteria and one row criteria
- Using a list (range) of column criteria (not single cells) in FILTER formula
- How to convert a table containing values and frequency to a single list in Excel 2016?
- Avoid "implicit intersection" symbol addition to Excel formulas when using MATLAB Actxserver
- Multiple ranges in RSQ Function formula
- Extract word from a delimited string
- Calculate average and ignore zero values when data is not structured in a range
- Daytime and Nightime Occurrence Duration of an Event
- How can I get a list of boxes at each site
- Compare both the columns from Text values