Requiero generar reportes de niveles de servicio (atención y solución) de tickets por medio de Excel.
La cobertura de servicio es lunes a sábado de 07:00 am a 07:00 pm
El nivel de servicio para la atención es de tres horas
y el tiempo de solución es de 8 horas.
El problema es que una simple resta no me sirve ya que requiero descontar los horarios que están fuera de cobertura de las 07:01 pm a las 06:59 am del día siguiente además de excluir todo el domingo. La formula “dias.lab” tampoco me funciona porque este cuenta días completos y se requiere que el resultado sea mas detallado en horas y minutos para confirmar si el reporte estuvo dentro de los niveles acordados.
La base de datos me da la fecha y hora de asignación, la de atención y la de cierre.
Adjunto archivo con los elementos.
| Adjunto | Tamaño |
|---|---|
 ejemplo_de_tiempos_para_niveles_de_servicio.xlsx | 15.88 KB |
Entiendo que una resta sí te sirve si las dos anotaciones son del mismo día, el día es la parte entera del número que representa la fecha, así que por ejemplo...
=SI(ENTERO(tufechahorainicial)=ENTERO(tufechahorafinal);fechahorafinal-fechahorainicial;.......)
Ahora nos queda saber qué hacemos cuando los datos no son del mismo día
Como tu horario laboral es de 7 a 19, habría 12 horas de diferencia entre el resultado de hacer la resta directamente y el tiempo que realmente tienes queconsiderar
Pero ¿qué ocurre si tardas otro día más en responder? en este caso la resta contaría otras 12 horas de más.
así que podríamos descontar 12 horas no laborables por cada día de diferencia que haya entre la fecha final e inicial.
(12 horas son 0.5días, 12/24)
podríamos modificar la fórmula y dejarla algo parecido a esto:
=fechahorafinal-fechahorainicial-12/24*(ENTERO(tufechahorafinal)-ENTERO(tufechahorainicial))
esta fórmula nos vale también para el caso de horas en el mismo día, porque entonces esa operación de diferencia de días es cero y por tanto no afecta al resultado.
Nos queda el problema de los domingos.
Si entre la fecha de inicio y la fecha de fin cae un domingo, hay que descontar también las 12 horas no laborables del domingo.
¿cómo sabemos si hay un domingo?¿podemos tener casos de más de un domingo entre una fecha y otra?
sabemos si hay un domingo entre medias si cambia la semana
para saber en qué semana estamos, Excel tiene la función NUM.DE.SEMANA (que cuenta las semanas empezando en domingo, nos vale)
así que podríamos calcular el número de la semana final, menos el número de la semana inicial, y el resultado será el número de domingos que hay en medio
así que restaríamos a la fórmula de antes
-12/24*(NUM.DE.SEMANA(tufechahorafinal)-NUM.DE.SEMANA(tufechahorainicial))
aún hay un problema más, ¿qué ocurre cuando cambia el año? en este caso tendríamos que empezamos en la semana 53 y acabamos en la semana 1, aunque puede que el cambio de semana se produzca en un miércoles
yo le añadiría 52 semanas a la fecha final y listo, para saber si cambia el año tenemos la función AÑO
así que retocaría lo anterior por ejemplo así:
-12/24*(((AÑO(fechahorafinal)-AÑO(fechahorainicial))*52+NUM.DE.SEMANA(tufechahorafinal)-NUM.DE.SEMANA(tufechahorainicial)))
el resultado de tu fórmula podría ser algo así:
=fechahorafinal-fechahorainicial-12/24*(ENTERO(tufechahorafinal)-ENTERO(tufechahorainicial))-12/24*(((AÑO(fechahorafinal)-AÑO(fechahorainicial))*52+NUM.DE.SEMANA(tufechahorafinal)-NUM.DE.SEMANA(tufechahorainicial)))
pruébalo con distintas fechas y nos cuentas qué tal
------
Ya sé Excel, pero necesito más.