¿Como restringir la fecha para hacer calculos de niveles de servicio?

2 envíos / 0 nuevos
Último envío
charlysauza
Imagen de charlysauza
Offline
última acción: Hace 6 años 6 meses
alta: 12/10/2012 - 18:25
Puntos: 30
¿Como restringir la fecha para hacer calculos de niveles de servicio?
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.

Etiquetas: 

pacomegia
Imagen de pacomegia
Offline
última acción: Hace 11 horas 42 mins
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntosadministrador
alta: 27/12/2006 - 23:26
Puntos: 10005
Re: ¿Como restringir la fecha para hacer calculos de niveles ...

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

 

Data Tools Suite
datos y tablas con Excel