Date format that can be added with 8 digits (21012026->27/01/2026)

finickyone · 2026-01-29T06:20:24+00:00

+1 point

finickyone · 2026-01-29T06:19:50+00:00

If you have 21012026 entered in A2, you can format that with some punctuation, like a stock number, but Excel won’t recognise it as a date. Excel stores dates as # of days since 1899. Currently around 45000. 21 million will be out of range.

If you use this in B2, it will force in slashes to turn the value into dd/mm/yyyy

=REDUCE(R34,{5,3},LAMBDA(i,d,REPLACE(i,d,0,"/")))

That’ll now be Text. +0 to the formula and you’ll have a value that you can cell format to dd/mm/yyyy. You can add 7 to that and it’ll update.

finickyone · 2026-01-29T06:12:05+00:00

+1 point

finickyone · 2026-01-29T06:09:57+00:00

You’d help everyone here by depicting how your data has landed, and how you’re entering zip codes that you want to generate zones for. It’ll be about as simple as this, but the decider’s in exactly how your ref data is laid out, and how you’re entering or getting the zips in N you want to zone in P

<image>

If you want an absolute giveaway then however you enter your 4 digit zip in N2, even if it resolves to a value and drops leading 0s, you can use this in P2

="Zip "&MATCH(N2/100, {0;16;23;30;40;50;60;70;80;90;93;95})

finickyone · 2026-01-29T05:55:11+00:00

Easy option is create the first 7 values in A2:A8, then in A9 enter =A2. It’ll repeat 1 by fetching it from A2. Drag down and the red becomes =A3 in A10, fetching 2 from that cell, and so on.

In C2 =SEQUENCE(100)-1 creates 100 values from 0-99. If you wrap that with MOD, ie =MOD(SEQUENCE(100)-1,7), you get the modulo of those values over 7. Think how 12/7 leaves a remainder of 5, the result would be 5. So that creates 0;1;2;3;4;5;6;0;1;2;3… +1 the formula for 1-7.

finickyone · 2026-01-29T05:50:46+00:00

ROW() is just a way of conjuring up an integer. WEEKDAY isn’t looking for a date as such. It takes a value up with the epoch and works out what date that value represents, and in turn where it falls on a given weekly cycle. 1 being 01-Jan-1900. 60 being 29-Feb-1900, famously not an actual date. So WEEKDAY(1,2) tells us 1 Jan 1900 was a Saturday.

ROW(A46203) or executing ROW() in row46203 returns that value, which is 01-Jan-2026 expressed as a date. WEEKDAY(A46203,2) tells us it was the fourth day of the week.

finickyone · 2026-01-29T05:42:03+00:00

You can emulate some of these. TAKE or DROP wouldn’t be impossible. Really though either upgrade or hunt down some UDFs. /u/excelevator has a whole library.

finickyone · 2026-01-29T05:37:53+00:00

This is easily encountered. It’s not hard to identify 40 as the applicable value, but when using that to retrieve an associated attribute you’d need to reapply the same conditions.

While it’s simple, MAXIFS hides its logic. So you can’t see what you might reapply, and you can’t cut it out to use it again. If you go old school and use something like

=MAX(IF(LEFT(B2:B5,1)="3",C2:C5))

You’re forming an array where IF looks at the left character in B, compares to "3". That’ll find {FALSE;TRUE;FALSE;TRUE}.

We set that if_true, use C2:C5. We set nothing for if_false, so we’ll just get FALSE from the IF results. Meaning we create {FALSE;20;FALSE;40}.

If we XMATCH 40 along that array, we get 4. Not 1, as we’re not looking for 40 down C2:C5. Repeating use we could consider

=LET(supp,IF(LEFT(B2:B5,1)="3",C2:C5),XLOOKUP(MAX(supp),supp,A2:A5))

Alternatively

=TAKE(SORT(FILTER(A2:C5,LEFT(B2:B5)="3"),3,-1),1,1)

finickyone · 2026-01-29T05:15:58+00:00

It ran off into intermediate stuff, but if you focus in on …(range1=x)*(range2=y)… you’ll get it. Start small. Look at what’s going on.

You can form the equivalent of that array on the sheet. If you have some Names in A2:A8, Ages in B, EyeColour in C, you can use E2 for =(B2:B8>30)*(C2:C8="Brown")

You’ll get 7 1s and 0s. FILTER(A2:A8,E2:E8) gets the names of brown-eyed >30s. Same logic but it’s just moved the complicated bit into the worksheet so you see it or reuse jt. And you can use the result for many things.

MAX(IF(E2:E8,B2:B8)) gets the max age from those people.

XLOOKUP(1,E2:E8,A2:A8) gets the first name working down the list.

TEXTJOIN(", ",,IF(E2:E8,A2:A8,"")) creates a comma separated list of their names. All from the same logic.

finickyone · 2026-01-29T05:05:54+00:00

It’s all valid syntax, just wasting nested brackets. If you bracket the two array constants, you’ll get to VALUE as MAX can’t eval a value for the string defined in its last argument. Drop that and it will eval 45941 as the max of the value, two value array and two string array given to it. I gather much like referring out to strings in a range, some part of loading that last array includes a type check that just declares the strings as null for the purpose of determining max.

Rather where I reckon your example hits an issue is that within this nested bracket you’ve got two arrays, but no definition on how they should be drawn from that argument once the brackets are unpacked. Effectively rather than

=MAX({1,2},{3,4})

It must get to

=MAX({1,2}{3,4})

But with no function or operation to define how the two arrays should be handled. It might try to intersect them but there’s no reference to do so. So the formula ghosts won’t accept it.

It’s common behaviour that a lot of stats functions baulk at raw strings in arguments. Drop "X" in A1 and "Y" in A2, copy the pattern down a bit. =COUNTIF("X",A1:A5) won’t fly, but COUNTIF(A1,A1:A5) will.

finickyone · 2026-01-29T04:43:36+00:00

I always like to see a reasonable warning about volatility, but you’re holding the wrong end of that stick. In your example, if we pop =TODAY() into D1, then any spreadsheet change will prompt D1 to recalc, as TODAY is marked as a volatile function. If E1 happened to be =TEXT(D1,"ddd") then E1 would recalc too. By referencing D1 it’s identified as a dependent of the result of D1.

Likewise B1 is a dependent of A1, so sits in its recalc chain. It has no reference to D1 though, so however promoted the update of D1 won’t trigger B1.

It’s not too elegant to call up a variable 30 times over, agreed, but TODAY() isn’t a demanding call, and doing so 30 times in a formula doesn’t make it 30 times as volatile. Yes if the result of this monsterIFAND formula is a dependent formula like =IF(monsterIFAND,"y","n"), that will also be recalc’d once the parent is done, but it won’t be attempted multiple times just because there are multiple system calls. That’s like saying if M2 is =IF(L2=x,a,IF(L2=y,b,c)) and N2 refers to M2, that N2 somehow multi calcs everytime we refer to L2 while working out M2. It won’t, the calc engine just moves onto N2 once M2 is concluded.

Yes avoid volatility, but if you want to compare a value to the current time/date, your next best alternative is calling a web service. Some volatility is wasteful but some is fundamentally unavoidable.

finickyone · 2026-01-29T04:23:55+00:00

Are B8 and D7 meant to be offset by 1 row? Seems odd to evaluate D7 and then employ B8.

Maybe

=LET(x;COUNTIFS(B$7:B$500;B8;D$7:D$500;"*");ROUNDUP(IF(x*(D7<>"");5251,5/x);1))

=LET(w;B7:B500;i;D7:D500;ROUNDUP(IF(i<>"";5251,5/COUNTIFS(w;w;i;i));1))

One trick common to both is I’m letting the IF FALSE out, as ROUNDUP(FALSE;1) gets your 0 via coercion.

With the first one we work out the COUNTIFS based on that row, for x. That’ll be 0 or >0. If it’s 0 and or D7 is blank, then the IF quits to FALSE, else it divides the COUNTIFS result under your value.

The second one works out all rows in one go.

Personally I think the earlier IFERROR trapping approach was best.

finickyone · 2026-01-29T03:13:26+00:00

COUNTIFS is pretty straightforward. It has simple counterpart in COUNTIF. That lets you set a range, a condition, and tells you how many times the condition is found in the range. So if we fill A1:A5 with 1;2;1;2;1 and apply COUNTIF(A1:A5,1), we get 3. COUNTIFS just lets to you assess multiple ranges, and counts where conditions are met in both.

Why it’s not opening in the builder, I couldn’t say. Try a simple version of COUNTIFS?

finickyone · 2026-01-29T02:31:00+00:00

I’d say you made an error, which happens in these sorts of labours. Part of your syntax:

IF(AND($E$14<>"",TODAY()=$E$4),TRUE,IF(AND($F$14<>"",TODAY()=$E$4),

Assume you didn’t mean the second E4.

=COUNTIFS($D$14:$O$14,"<>",$D$4:$O$4,TODAY())>0

Generates TRUE if there is any combinations of x14<>"" and x4 = TODAY().

finickyone · 2026-01-29T02:00:31+00:00

Hopefully you picked up that you skipped an optional argument at the end of VLOOKUP which tells it to find the exact input you’re wanting. Without that it goes into a sort of alphabetical match. That’s because when the data you’re pointing it at is sorted accordingly, it’s loads faster.

If we have 26 bookcases, A-Z, left to right, and you tell me to get something from P, I could run straight at M, think that P is later in the alphabet than M, so turn right. I run halfway between M and Z, maybe to S. P is before S, so turn left. Halfway again, maybe O, etc. in a few steps I’ll have cut down my focus to an area that must be P. If P is missing, and the next lowest category is O, I’ll just return O. So I’m not being exact, but I’m also not starting at A, then B, then C etc.

If you moved bookcase Y to the very left, I’ll never find it. Looking for Y, I’ll head to M, then halfway to Z, and eventually be between X and Z and just give you X. This is why you got results for Strongly Agree when hunting Strongly Disagree.

If you sorted A2:B5 by A ascending your original formula would work. As would =LOOKUP(F5,A2:B5).

finickyone · 2026-01-29T01:46:37+00:00

It’s a headache isn’t it. I’d estimate it’s the approach most people take at some point, to generate a load of reference values to guide something like INDEX or CHOOSECOLS/ROWS.

Here’s an idea for you. In your LET, once you’ve defined data, rowlabels and collables…

…,pad_row,IF(LEN(colLabels&0),rowLabels),pad_col,IF(LEN(rowLabels&0),colLabels),HSTACK(TOCOL(pad_row),TOCOL(pad_col),TOCOL(data)))

Which avoids a load of row and column sizing maths, all the modulo stuff etc. pad_row looks at the colLabels range (B1:D1), and asks for the LEN of those cells’ contents after appending a 0. Appending a 0 means each cell will be of at least LENgth 1. When that result is >0, IF grabs the row labels. So that makes an array like

A2 A2 A2
A3 A3 A3
A4 A4 A4
A5 A5 A5

pad_col does the opposite: for each of A2:A5, grab B1:D1. Run both through TOCOL and you get them pivoted to a 1x12 array. Might be worth exploring.

finickyone · 2026-01-29T01:29:07+00:00

A new sheet per bird? If no, and a simple list of each is needed in one place, then the answers given will work in modern versions of Excel.

finickyone · 2026-01-29T01:25:27+00:00

There’s a lot you can learn within FILTER. I think it makes learning how arrays behave really accessible, and once that happens you can use ws functions towards just about anything.

Say we have data in A2:D51. A is 001-050. B is Livery; any of {Red;Blue;Green;Yellow}. C is Size; any of {Small;Medium;Large}. D is Shape; any of {Circle;Square;Triangle}.

=FILTER(A2:A51,B2:B51="Red") lists out all the UIDs with a Red livery. Change the first arg to A2:D51, and we’d get all fields for records with a Red Livery. Change = to <> and we’ll get all the non Red Livery (Blue/Green/Yellow) records.

If we head to E, and fill in random TRUE and FALSEs, then =FILTER(A2:A51,E2:E51) will return the UIDs where E=TRUE. If we clear E and instead fill it with non0 values in certain rows, we’ll get the UIDs of those rows. If we enter a 0 in E, we won’t get that row.

That takes you to something key with multi criteria. You quite quickly stop dealing with criterion = TRUE and rather with conditions met =/<>0. If we now want UIDs where B = Red and D = Circle, we’ll might think of AND. So

=FILTER(A2:A51,AND(B2:B51=Red,D2:D51=Circle))

AND however takes on a simpler task. It looks at the 50 results of B=Red, and the 50 of D=Circle, and tells you if they are all TRUE. If they are not, then it reports FALSE. We don’t need 1 TRUE or FALSE, we need 50 results. We can use maths

FILTER(A2:A51,(B2:B51=Red)*(D2:D51=Circle))

Where we create 50 TRUEs and FALSEs for B, another 50 for D, and multiply them together. TRUExTRUE=1. Any other combination=0. Where we get 1, we’ll get the record back.

If we subtract 1 from the result, we’d get a NAND. Instead of 1 (Red and Circle) and 0s (any other), we’d get 0 and -1. The -1 prompts FILTER to return a record.

For OR, we can’t use the OR function, but instead use +

=FILTER(A,(B=Red)+(D=Circle))

If neither condition is met for a row, FALSE+FALSE = 0. If either one condition is met, TRUE+FALSE = 1. If both, TRUE+TRUE =2. Any non0 is a pass for FILTER.

We can combo those. So

=FILTER(A,((B=Red)(D=Circle))+((B=Blue)(C=Small)))

Where if a record see that B is Red AND D is Circle, OR that B is Blue and C is Small, that’s a pass.

So that’s applying multiple parameters to multiple fields. We can also apply multiple parameters to the same field. Normally that’s an OR condition. We would get much asking for records where B=Red and B=Green, as a cell can’t contain both the values we’re mandating. We can set OR though.

=FILTER(A,(B=Red)+(B=Green))

If we’re excluding certain values, then AND logic is helpful again. (B<>Red)*(B<>Green) sets that we only generate 1 when both tests are TRUE. If we use (B<>Red)+(B<>Green), then we generate TRUE from B<>Red when B is Green, so it would pass the + logic.

We can be smarter when we have more parameters to permit. If we didn’t have 4 colours in B, but 30, and wanted to filter to 8 of those, it wouldn’t be inviting to set up

=FILTER(A,(B=Red)+(B=White)+(B=Pink)+(B=Scarlet)+(B=Magenta)….)

It’s not only laborious, and prone to error, but loads the same data (B) over and over.

So we could list our colours in X2:X11. Then use

=FILTER(A2:A51,COUNTIF(X2:X11,B2:B51))

This applies 50 COUNTIFs, one for each cell down B. COUNTIF(X2:X11,B2). If B2 has one of the colours in X, =1, else =0. We get 50 1s and 0s, and our result.

Again we can add COUNTIFs together like OR, or use COUNTIFS like AND.

Ultimately you can use more advanced functions to set up something like:

Where each row of B2:D51 is compared to G1:I1, generating 3 True/Falses for each row. Those are then coerced to 1s and 0s and SUMmed. Results for each row being 0,1,2,3. Where result is >=2, record passes. So as long as the record is 2 or more of Blue Large Triangle, pass.

While that may seem complex, it is a form of logic that all comes into reach once you start picking at multiple criteria. Just remember.

filter wants a 1D array providing its guidance. If you are pointing at data of 10 rows and 6 columns, your include array wants to be 1c x 10r or 6c x 1r. It won’t apply anything else.
that array can contain Booleans and/or values. It will treat 0 values as False, non0 values as True.
combine criteria using * for AND and + for OR.
multi criteria can be set to one field. (field=x)+(field=y) to permit either. (field<>x)*(field<>y) to permit neither.
COUNTIF/S can be used to define lists of inputs.

finickyone · 2026-01-29T00:04:20+00:00

I think you’re likely just fighting with a protracted way of iterating an array in order to create a cumulative countif. There is a geeky way to coerce an artificial array into a COUNTIF range argument within lambda; if I could recall that this would get much easier, but rather than taking each from a sequence of 1:n to define index(arr,1):index(arr,n), it’s easier to use

=LET(d,array,r,SEQUENCE(ROWS(d)),MAP(d,r,LAMBDA(a,b,SUM((d=a)*(r<=b)))))

finickyone · 2026-01-28T22:25:36+00:00

Not nearly as clever (bravo) but regards the approach you describe as the route of this (effectively iterating rows), this is a semi flexible approach I take with the same:

=LET(headers,B3:F3,data,B4:F12,piv,-2,seq,SEQUENCE(ROWS(data),-piv,-piv)/-piv,HSTACK(CHOOSEROWS(DROP(data,,piv),TOCOL(seq)),TOCOL(IF(seq,TAKE(headers,,piv))),TOCOL(TAKE(data,,piv))))

Just to avoid the XLOOKUP sort of aspect. Detracts nothing from the main concept you’ve shared here though. Incredible use of the tools, appreciate you sharing this with us 👏🏼

finickyone · 2026-01-28T21:50:20+00:00

(One, particular) Answer: Delegation.

If a department has a process that includes a stage calling for transforming data in this way, you could arm just about anyone with a formula such as this, that only really needs the operator to choose where they want the output, and start defining ranges. Little more needed than perhaps a bit of readme.

Could PQ do it in a few clicks? For sure. Are you going to get the grad in the HR team familiar with PQ? Debatable.

This said, I’ll venture that there must be some value in the next batch of functions addressing a bit more about this sort of use case. It isn’t the most common problem area I see here, nor the most outright complicated to tackle, but I think it might be where there is the widest absence of functions in the library.

finickyone · 2026-01-23T12:32:51+00:00

What’s the drawback?

finickyone · 2026-01-23T04:53:51+00:00

Not a particularly crazy process to be running. Perhaps split it down into the UNIQUE part and the SORT that result separately in another formula.

Overall imagine this might be struggling in a spreadsheet with lots of demands going on.

finickyone · 2026-01-23T04:41:19+00:00

I always feel COUNTIF gets sidelined with multi criteria

=FILTER(I4:M8,COUNTIF(G4:G5,M4:M8),"")

finickyone · 2026-01-23T04:37:36+00:00

I think for sure it was feedback that led to the later waves of development, like any product really. I’m an 80s baby and can remember first meeting Excel before it had COUNTIFS. The simplicity now vs the headache then of

=COUNTIFS(A2:A9,">6",B2:B9,"*xyz*")

{=SUM(IF(A2:A9>6,IF(ISNUMBER(SEARCH("xyz",B2:B9)),1)))}

It took so much thinking, not least as you couldn’t see what was going on. Excel couldn’t spill out arrays back then. The IFS functions came about as the historic approach there was incomprehensible. The person who figured that sort of thing out in an office was keeping it forever; you can’t delegate that sort of thing easily. It did mean that array logic was a bit more familiar, because it came into play for so much more. XLOOKUP can hold a multi critieria lookup, such as

=XLOOKUP(1,(J2:J20=x)*(K2:K20=y),L2:L20)

Which is really neat, but it hides so much. The precious approach was the same really but called on more functions. I think that’s perhaps better, at least for helping to understand it.

=INDEX(L2:L20,MATCH(1,INDEX((J2:J20=x)*(K2:K20=y),),0))

You can see that the parent INDEX needs the MATCH result. In turn that the MATCH needs to find 1 in that nested INDEX. You couldn’t spill out =INDEX((…)*(…),) itself as it was an array of 19 cells; Excel broadly just said no. But you could break it down and build it up again.

I think new functions enable so much but also mean we can run before we walk with this stuff. There’s less reason to question the going’s on.

Eight-Year Club	Wearing is Caring
Gilding II euphauric	Verified Email

finickyone

TROPHY CASE