¿Cómo puedo utilizar las funciones CONTAR.SI y HOY para identificar fechas límite de trabajo en varias hojas de trabajo?

Question

He estado jugando con nuestras hojas de cálculo en el trabajo y he podido crear una fórmula básica que identificará cuándo hay una fecha límite en la fecha de hoy que debe completarse. El problema que tengo ahora surge porque las personas quieren saber cuándo han cumplido los plazos.

La fórmula que he creado funciona muy bien para instancias únicas, pero no es lo suficientemente inteligente como para utilizarla cuando hay múltiples plazos: cuando se completa un plazo, muestra todos los plazos como completos.

Quiero evitar hacer referencia a cada celda cuando sea posible; sin embargo, si hay una manera de hacer que la fórmula identifique que si la celda a la izquierda de la que contiene la fecha de hoy todavía está en blanco, el trabajo debe completarse. Cuando se ingresa la fecha de hoy en esa celda, el trabajo estará completo.

El ejemplo de la fórmula es:

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

S5es el nombre de la pestaña del libro de trabajo, Column Oa la que se hace referencia y sería esta columna donde se pone la fecha de hoy cuando se completa.

Efectivamente, debe decir que si dice la fecha de hoy en Column Po Qy Column Oestá en blanco, todavía muestra la palabra Acción, incluso si se ha completado otro trabajo ese día.

Answer 1

Prueba esto:

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

Algunas notas estilísticas:

• Generalmente prefiero la CONCATENATEfunción en lugar de romper las cadenas de salida junto con el signo comercial. Sin embargo, la fórmula debería funcionar con cualquiera de los dos.
• Para referencias a celdas en la misma hoja de trabajo, no es necesario definir el nombre de la hoja de trabajo. Si coloca la fórmula en una hoja que no sea 'SS', deberá agregar la referencia de la hoja.

Qué hace la fórmula:

1. En lugar de utilizar símbolos comerciales, la CONCATENATEfunción se utiliza para ensamblar la salida de las fórmulas internas.
2. La primera parte de la salida de la fórmula se define según los resultados de una IFfunción.
3. COUNTIFse utiliza para contar el número de celdas en las columnas P y Q que coinciden con la fecha de hoy.
4. Si COUNTIFdevuelve cero, IFtratará la respuesta como FALSA. Cualquier otro valor se trata como VERDADERO.
   • Nota: Si las celdas no tienen formato de fechas o se agregan elementos de hora, es posible que se devuelvan resultados incorrectos.
5. Si la primera IFdeclaración se evalúa como VERDADERA, la primera parte de la cadena de salida será "Acción". De lo contrario, la primera parte de la cadena de salida será "Sin acción".
6. La segunda parte de la salida de la fórmula se define de acuerdo con los resultados de otra IFfunción.
7. COUNTIFse usa dos veces aquí: primero para verificar cuántos elementos en la columna O coinciden con la fecha de hoy, luego para verificar nuevamente cuántos elementos en las columnas P y Q coinciden con la fecha de hoy. COUNTIFLuego se comparan los dos mensajes para proporcionar el estado VERDADERO/FALSO de la IFfunción.
8. SI los resultados de COUNTIFs coinciden exactamente, IFse evaluará como VERDADERO; de lo contrario, FALSO.
   • Nota: Si las celdas no tienen formato de fechas o se agregan elementos de hora, es posible que se devuelvan resultados incorrectos.
9. Cuando la declaración IF se evalúa como VERDADERA, la segunda parte de la cadena de salida será "Completada". De lo contrario, estará en blanco.

Vale la pena señalar que todavía existe cierta falacia en este método. En particular, si el trabajo se registra como completado hoy pero no debía entregarse hoy (trabajo completado antes o después de lo previsto), entonces el resultado de hoy puede mostrar "Completado" aunque todavía haya otros trabajos pendientes de entrega hoy. De manera similar, si todo el trabajo que debía realizarse hoy o antes se completó hoy o antesyparte del trabajo que debía entregarse mañana también se completó, el resultado nunca mostrará "Completado" mañana a menos que se complete una cantidad igual de trabajo antes de tiempo mañana. Además, aquí no hay ninguna lógica para contabilizar los artículos vencidos que aún están pendientes.

Algunos ejemplos de casos de la falacia:

• Hoy vencen 10 proyectos. 8 de los proyectos de hoy se completaron hoy. Los otros 2 proyectos que vencen hoy no están completados. Hoy se completó 1 proyecto retrasado desde ayer. 1 proyecto que debía entregarse mañana se completó antes de tiempo hoy. El resultado de la fórmula será "Acción completada" aunque 2 proyectos que vencen hoy aún no se han completado.
• Hoy vencen 10 proyectos. 3 de esos proyectos se completaron ayer. Los otros 7 proyectos se completaron hoy. No se completó ningún otro trabajo hoy. Aunque todos los proyectos de hoy están completos, el resultado todavía no dirá "Completado".
• 10 proyectos vencen hoy y se completaron hoy. No se completó ningún otro trabajo hoy. Sin embargo, todavía hay 5 proyectos sin completar que debían entregarse antes de hoy. El estado mostrará "Acción completada" aunque todavía quede trabajo por hacer.

Sin saber con mayor precisión qué columnas representan P y Q, o sin estar más familiarizado con sus requisitos, sería un poco difícil encontrar una fórmula que refleje más adecuadamente un estado preciso. Sin embargo, dicha fórmula probablemente implicaría el uso de al menos una columna adicional (para realizar un seguimiento por separado del trabajo completado para cada fecha límite y/o evaluar individualmente el estado de cada partida) y algún uso de COUNTIFS.