Obtenga la suma de las celdas que contienen “Vacation”, excepto cuando la fecha relacionada coincide con cierto día y mes.
De manera simplificada, tengo dos columnas: Fecha y Texto; quiero verificar mi cantidad actual de días de vacaciones basándome en la primera fecha en la fila 2, así que se me ocurrió la siguiente fórmula:
="Días de vacaciones disponibles: "&AÑO.FRAC(A2;HOY())*12*(25/12)
Calculo la fracción del año basada en la primera fecha y la fecha de hoy, lo multiplico por 12 para obtener meses y lo multiplico nuevamente por la cantidad total de días de vacaciones en mi contrato por mes. Ahora tengo otra fórmula para recopilar todas las celdas en la columna B que contienen “Vacaciones”, bastante sencillo:
=CONTAR.SI(B:B;"Vacaciones")
Ahora la parte interesante: tengo la fórmula que me da un valor booleano si una fecha y hora coinciden con el 24 o el 31 de diciembre:
=Y(O(DIA(A53)=24;DIA(A53)=31);MES(A53)=12)
Quiero contar los días de vacaciones que ocurren el 24 o el 31 de diciembre como medio día de vacaciones (0.5), y de lo contrario contarlos como un día completo (1). Luego quiero combinar mi primera declaración con este resultado y restar los días de vacaciones utilizados. Leí acerca de VLOOKUP y XLOOKUP, pero no estoy seguro si se ajustan a este propósito. Quiero evitar tener una columna adicional con mis valores booleanos y preferiría que esta celda me proporcione toda la información combinada.
davy.ai
Para lograr el resultado deseado, puedes utilizar la siguiente fórmula:
= "&Días de vacaciones disponibles: " & ((YEARFRAC(A2, HOY()) * 12 * 25/12) - CONTAR.SI.CONJUNTO(B:B, "Vacaciones", A:A, "<>"&"", A:A, "<>="&FECHA(AÑO(A2),1,1), A:A, "<="&FECHA(AÑO(A2),12,31), O(DIA(A:A)=24, DIA(A:A)=31), MES(A:A)=12) - (CONTAR.SI.CONJUNTO(B:B, "Vacaciones", A:A, "<>"&"", A:A, ">="&FECHA(AÑO(A2),1,1), A:A, "<"&A2)) + (0.5 * CONTAR.SI.CONJUNTO(B:B, "Vacaciones", A:A, "<>"&"", A:A, ">="&FECHA(AÑO(A2),1,1), A:A, "<>"&FECHA(AÑO(A2),12,24), A:A, "<>"&FECHA(AÑO(A2),12,31), O(DIA(A:A)=24, DIA(A:A)=31), MES(A:A)=12)))Esta fórmula calcula los días de vacaciones disponibles como antes, y luego resta tres contajes diferentes para darte el número final.
El primer conteo resta todos los días de vacaciones que caen el 24 o el 31 de diciembre, multiplicando por 0.5 para obtener el conteo de medio día.
El segundo conteo resta todos los días de vacaciones que caen dentro del año (1 de enero al 31 de diciembre) que ocurren antes de la primera fecha registrada.
El tercer conteo resta todos los días de vacaciones que caen dentro del año (1 de enero al 31 de diciembre) que ocurren después de la primera fecha registrada, excluyendo el 24 y 31 de diciembre.
Esto te da el número total de días de vacaciones utilizados durante el año actual. Puedes ajustar el rango de fechas en las diversas funciones para que se ajusten a tu situación específica.
En general, VLOOKUP o XLOOKUP no son necesarios para esta fórmula, ya que no implica buscar valores en otra tabla. Todo se calcula en base a los datos en las dos columnas proporcionadas.