Sobre este exercício
Um conjunto de dados de 200 linhas em uma planilha, 2 células de resposta, cerca de 12 minutos. Corrigido no seu navegador: o valor e a fórmula por trás dele, com feedback que mostra o que corrigir.
Como resolver
- D2
=IF(LEN(C2)=8,DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)),IF(MID(C2,3,1)=".",DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2)))) - H2
=COUNTIFS($D$2:$D$201,">="&G2,$D$2:$D$201,"<="&EOMONTH(G2,0))
A date that arrives as text is just characters to Excel, so the fix is to rebuild it: DATE(year, month, day) returns a genuine date serial from three numbers, and LEFT, MID and RIGHT supply those numbers from the right positions of the string. Because the three systems use three fixed shapes, two tests are enough to tell them apart — an 8-character string is yyyymmdd, a dot in position 3 means dd.mm.yyyy, otherwise it is mm/dd/yyyy — and IF routes each row to the matching DATE call. Once column D holds real dates, COUNTIFS can count a month as "on or after the first day and on or before the last day", which EOMONTH(G2,0) provides.
Pitfall 1: DATEVALUE depends on the regional settings of the machine — in a US locale it reads 03/14/2026 but returns #VALUE! for 14.03.2026 and 20260314 (and in a German locale the opposite happens). Rebuilding with DATE is locale-proof, which is why it is the method of record for mixed exports. Pitfall 2: DATE accepts the text pieces ("2026") and converts them itself, but a stray space or a non-breaking space in the source turns the piece into #VALUE! — wrap C2 in TRIM when the export is dirty. Pitfall 3: the converted column shows serial numbers until you format it; and if you later type a date over a formula cell, that cell stops updating — keep a text column and a converted column rather than overwriting. Variants: =--TEXT(C2,"0000-00-00") converts the 8-digit shape in one step; =DATEVALUE(MID(C2,4,2)&"/"&LEFT(C2,2)&"/"&RIGHT(C2,4)) rebuilds the German shape as a US string (locale-dependent again). Text to Columns with the "Date: DMY/MDY/YMD" setting converts one shape at a time without formulas, and Power Query does all three with a locale per column. Grouping: COUNTIFS with a date range is the pivot-free way to count per month; SUMPRODUCT((YEAR($D$2:$D$201)=YEAR(G2))*(MONTH($D$2:$D$201)=MONTH(G2))) gives the same result and extends to any grouping. A pivot table groups dates by month with one click — but only on real dates, never on text.
Outras formas que funcionam
- =IF(B2="ERP",DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)),IF(B2="DE warehouse",DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2)))) — decide by the Source column instead of the string shape
- =IFS(LEN(C2)=8,DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)),ISNUMBER(FIND(".",C2)),DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)),TRUE,DATE(RIGHT(C2,4),LEFT(C2,2),MID(C2,4,2))) — IFS instead of nested IF (Excel 2019 and 365)
- =SUMPRODUCT((YEAR($D$2:$D$201)=YEAR(G2))*(MONTH($D$2:$D$201)=MONTH(G2))) in H2 — month count without COUNTIFS criteria strings
Erros comuns
Relying on DATEVALUE: =DATEVALUE(C2) converts the 99 US-shaped rows and returns #VALUE! for the 101 German and ERP rows — the format a system writes must be parsed explicitly.
Como corrigimos
Qualquer fórmula que produza o resultado certo é aceita, a menos que a tarefa cite uma função: nesse caso, outra função vale metade da pontuação. Comparamos os valores com uma pequena tolerância e ignoramos maiúsculas e espaços no texto. Um número certo digitado à mão em vez de uma fórmula vale metade da pontuação. Tarefas com várias células valem a pontuação integral com 100% das células certas e metade a partir de 50%. Quando faz diferença, também mudamos uma célula de entrada em uma cópia da sua planilha e verificamos se sua fórmula acompanha.
Respostas calculadas com as regras do Excel 365 e verificadas no nosso corretor em 16 de novembro de 2026. Autor: GridBelt team.