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))
- Excel formula for calculating difference between two date columns
- How to get the cartesian product (cross join) of two sets via Excel Formulas
- Sum of text Does not contain in Range
- Consolidated Date window
- Convert Time Input in Decimal Format - Excel Formula or VBA
- Tiling an Excel array in 2 dimensions, ideally not using VBA
- VSTACK doesn't pile up dynamic ranges
- EXCEL: Count all unique values in Table column up to a certain point, and combine them into a single string?
- Trying to calculate user whos spent the most and the least - Excel
- Is there a way to create a simple custom formula in Excel without VBA?
- Excel VLOOKUP Doesn't Recognize Identical Cells
- vlookup with formula calculated from lookup cell location
- Excel List Parse Table
- Extract number from the end of text string in Excel
- Equivalent of Excel's Weeknum function in Python
- How to Rank Items by Multiple Criteria in Excel
- Extracting digits from a cell with varying char length
- Find the minimum value of a range based on presence of a substring in another range
- Excel: Extract a sequence of n numbers from a string with a formula
- #DIV/0! error with AVERAGEIFS formula. How to convert to '0'?
- Is possible find the value not equal to one in the column and get all position in the same field?
- Excel: Sum duplicates values only once and only on visible cells
- Excel: Transforming automatically a cell based on the input in another one
- How to convert a text with data in series having field name on left to a table in Excel?
- Excel Formula: how to split string by capital letters
- Is there a quicker way to calculate the cells highlighted in green? [SOLVED]
- First Name & Last Name drop down list
- How to generate Parent/Child columns from hierarchy in Excel
- Search multiple Excel sheets for values and exclude from list
- Excel - how to return next highest figure in a column but only from a row below that specific number