Como posso usar as funções COUNTIF e TODAY para identificar prazos de trabalho em várias planilhas?

Question

Eu tenho jogado 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. A questão que agora tenho vem das pessoas que querem identificar quando completaram os prazos.

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

Eu quero evitar fazer referência a cada célula quando possível, no entanto, se houver uma maneira de fazer a fórmula identificar que a célula à esquerda da que contém a data de hoje ainda está em branco, que o trabalho deve ser concluído. Quando a data de hoje é inserida nessa célula, o trabalho é 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 guia da pasta de trabalho, Column O é referenciado e seria essa coluna onde a data de hoje é colocada quando concluída.

Efetivamente, é necessário dizer que, se estiver dizendo que a data de hoje em Column P ou Q e Column O está em branco, ela ainda mostra a palavra Ação, mesmo que outro trabalho nesse dia tenha sido concluído.

    

Answer 1

Tente isto:

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

Algumas notas estilísticas:

• Geralmente, prefiro a função CONCATENATE em vez de quebrar as strings de saída junto 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 você colocar a fórmula em uma planilha diferente de 'SS', precisará adicionar a referência da planilha.

O que a fórmula faz:

1. Em vez de usar o E comercial, a função CONCATENATE é 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 função IF .
3. COUNTIF é usado para calcular o número de células nas colunas P & Q que correspondem à data de hoje.
4. Se COUNTIF retornar zero, IF tratará a resposta como FALSE. Qualquer outro valor é tratado como TRUE.
   • Nota: Se as células não forem formatadas como datas ou se forem adicionados elementos de hora, poderão ser retornados resultados incorretos.
5. Se a primeira instrução IF for avaliada como TRUE, a primeira parte da sequência de saída será "Action". Caso contrário, a primeira parte da sequência de saída será "Nenhuma ação".
6. A segunda parte da saída da fórmula é definida de acordo com os resultados de outra função IF .
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 & Q corresponde à data de hoje. Os dois COUNTIF s são então comparados, para fornecer o status TRUE / FALSE para a função IF .
8. SE os resultados de COUNTIF s coincidirem exatamente, IF será avaliado como TRUE, caso contrário, FALSE.
   • Nota: Se as células não forem formatadas como datas ou se forem adicionados elementos de hora, poderão ser retornados resultados incorretos.
9. Quando a instrução IF é avaliada como TRUE, a segunda parte da string de saída será "Completed". Caso contrário, ficará em branco.

Vale a pena notar que ainda existe alguma falácia neste método. Particularmente, se o trabalho é registrado como concluído hoje, mas não estava previsto para hoje (trabalho concluído antes ou atrasado), então o resultado de hoje pode mostrar "Concluído", apesar de haver outro trabalho ainda devido hoje. Da mesma forma, se todo o trabalho vencido hoje ou anterior fosse concluído hoje ou antes e algum trabalho que estava previsto para amanhã também foi concluído, o resultado nunca será exibido "Concluído" amanhã, a menos que uma quantidade igual de trabalho seja completado antes do tempo amanhã. Além disso, não há nenhuma lógica aqui para contabilizar os itens atrasados ainda pendentes.

Alguns exemplos de casos da falácia:

• 10 projetos vencem hoje. 8 dos projetos de hoje foram concluídos hoje. Os outros dois projetos vencidos hoje não estão concluídos. 1 projeto vencido ontem foi concluído hoje. Um projeto que estava previsto para amanhã foi concluído antes do prazo de hoje. A saída da fórmula será "Ação concluída", embora dois projetos vencidos ainda não estejam 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 completos, a saída ainda não diz "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, vencidos até hoje. O status mostrará "Ação concluída", embora ainda haja trabalho a ser feito.

Sem saber com mais precisão quais colunas P & Q representa, ou está mais familiarizado com seus requisitos, seria um pouco difícil criar uma fórmula que reflita mais adequadamente um status preciso. No entanto, essa 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 .