Como posso usar as funções CONT.SE e HOJE para identificar prazos de trabalho em várias planilhas?

Question

Tenho brincado com nossas planilhas no trabalho e consegui criar uma fórmula básica que identificará quando há um prazo na data de hoje que precisa ser concluído. O problema que tenho agora decorre de pessoas que desejam identificar quando cumpriram os prazos.

A fórmula que criei funciona muito bem para instâncias únicas, mas não é inteligente o suficiente para ser usada quando há vários prazos - quando um prazo é concluído, ela mostra todos os prazos como concluídos.

Quero evitar referenciar todas as células sempre que possível, porém se houver uma maneira de fazer a fórmula identificar que se a célula à esquerda daquela que contém a data de hoje ainda estiver em branco, o trabalho deve ser concluído. Quando a data de hoje for inserida nessa célula, o trabalho estará concluído.

O exemplo da fórmula é:

=(IF(COUNTIF('S5'!P:Q,"="&TODAY()),"Action","No Action")&IF(COUNTIF('S5'!O:O,"="&TODAY())," Completed",""))

S5é o nome da aba da pasta de trabalho, Column Oé referenciada e seria esta coluna onde seria colocada a data de hoje quando concluída.

Efetivamente, ele precisa dizer que, se estiver informando a data de hoje em Column Pou Qe Column Oestiver em branco, ainda mostrará a palavra Ação, mesmo que outro trabalho naquele dia tenha sido concluído.

Answer 1

Experimente isto:

=CONCATENATE(IF(COUNTIF(P:Q,TODAY()),"Action","No Action"),IF(COUNTIF(O:O,TODAY())=COUNTIF(P:Q,TODAY())," Completed",""))

Algumas notas estilísticas:

• Eu geralmente prefiro a CONCATENATEfunção em vez de esmagar as strings de saída com o E comercial. No entanto, a fórmula deve funcionar com qualquer um deles.
• Para referências a células na mesma planilha, não é necessário definir o nome da planilha. Se estiver colocando a fórmula em uma planilha diferente de 'SS', você precisará adicionar a referência da planilha.

O que a fórmula faz:

1. Em vez de usar e comercial, a CONCATENATEfunção é usada para montar a saída das fórmulas internas.
2. A primeira parte da saída da fórmula é definida de acordo com os resultados de uma IFfunção.
3. COUNTIFé usado para contabilizar o número de células nas colunas P e Q que correspondem à data de hoje.
4. Se COUNTIFretornar zero, IFtratará a resposta como FALSA. Qualquer outro valor é tratado como TRUE.
   • Nota: Se as células não estiverem formatadas como datas ou se forem adicionados elementos de hora, resultados incorretos poderão ser retornados.
5. Se a primeira IFinstrução for avaliada como TRUE, a primeira parte da string de saída será "Action". Caso contrário, a primeira parte da string de saída será “No Action”.
6. A segunda parte da saída da fórmula é definida de acordo com os resultados de outra IFfunção.
7. COUNTIFé usado duas vezes aqui - primeiro para verificar quantos itens na coluna O correspondem à data de hoje e, em seguida, para verificar novamente quantos itens nas colunas P e Q correspondem à data de hoje. Os dois COUNTIFs são então comparados para fornecer o status TRUE/FALSE para a IFfunção.
8. SE os resultados de COUNTIFs corresponderem exatamente, IFserá avaliado como TRUE - caso contrário, FALSE.
   • Nota: Se as células não estiverem formatadas como datas ou se forem adicionados elementos de hora, resultados incorretos poderão ser retornados.
9. Quando a instrução IF for avaliada como TRUE, a segunda parte da string de saída será "Concluída". Caso contrário, ficará em branco.

Vale a pena notar que ainda existe alguma falácia neste método. Particularmente, se o trabalho for registrado como concluído hoje, mas não for devido hoje (trabalho concluído antes ou depois do prazo), o resultado de hoje poderá mostrar "Concluído", mesmo que ainda existam outros trabalhos devidos hoje. Da mesma forma, se todo o trabalho que era devido hoje ou antes foi concluído hoje ou antesealgum trabalho que deveria ser entregue amanhã também foi concluído, a saída nunca mostrará "Concluído" amanhã, a menos que uma quantidade igual de trabalho seja concluída antecipadamente amanhã. Além disso, não há nenhuma lógica aqui para contabilizar itens vencidos ainda pendentes.

Alguns exemplos de casos da falácia:

• 10 projetos vencem hoje. 8 dos projetos de hoje foram concluídos hoje. Os outros 2 projetos que vencem hoje não foram concluídos. 1 projeto atrasado de ontem foi concluído hoje. 1 projeto que deveria ser entregue amanhã foi concluído antes do previsto hoje. O resultado da fórmula será "Ação Concluída", embora 2 projetos com vencimento hoje ainda não tenham sido concluídos.
• 10 projetos vencem hoje. 3 desses projetos foram concluídos ontem. Os outros 7 projetos foram concluídos hoje. Nenhum outro trabalho foi concluído hoje. Mesmo que todos os projetos de hoje estejam concluídos, o resultado ainda não dirá “Concluído”.
• 10 projetos vencem hoje e foram concluídos hoje. Nenhum outro trabalho foi concluído hoje. No entanto, existem 5 projetos ainda não concluídos que estavam previstos para hoje. O status mostrará "Ação concluída", embora ainda haja trabalho a ser feito.

Sem saber mais precisamente o que as colunas P e Q representam, ou estar mais familiarizado com seus requisitos, seria um pouco difícil chegar a uma fórmula que reflita de maneira mais adequada um status preciso. No entanto, tal fórmula provavelmente envolveria o uso de pelo menos uma coluna adicional (para acompanhar separadamente o trabalho concluído para cada prazo e/ou avaliar individualmente o status de cada item de linha) e algum uso de COUNTIFS.