Cómo calcular las horas trabajadas de tus viajantes en Excel

Las planillas de cálculo, tanto Excel como Google Sheets, permiten desarrollar un sin fin de soluciones en términos de generación de datos e información en los negocios. Pero para encontrar el mejor arreglo en cada proceso es necesario entender las características de cada actividad y necesidad. A continuación, se presenta un procedimiento de cálculo de horas trabajadas de los empleados.

En este caso vamos a trabajar con un problema muy puntual: los viajantes. Son empleados que se mueven dentro y fuera de la ciudad, con horarios variables y tarifas distintas según la zona. Armar esa cuenta a mano o de memoria, es la forma más fácil de cometer errores y crear situaciones de conflicto.

Contexto del Ejemplo

1. Ingresan fecha y hora de entrada.
2. Ingresan fecha y hora de salida.
3. Se define la zona.
4. Se calcula la duración del viaje: cuántas horas trabajó cada uno.
5. Se muestra la tarifa base.
6. Se calcula cuánto le corresponde cobrar.

Paso 1: armar la tabla auxiliar de tarifas

Antes de empezar con la planilla principal, crea una hoja aparte llamada, por ejemplo: [TarifasZona]. Ahí vas a cargar la tarifa por hora según el tipo de viaje: si es dentro o fuera de la ciudad.

Este tipo de listas separadas se llaman tablas auxiliares: guardan datos que se repiten (tarifas, zonas, categorías) para no escribirlos a mano cada vez.

Paso 2: armar la planilla principal, por empleado

En una nueva hoja, carga una fila por cada viaje realizado. Necesitaremos estas columnas:

• Empleado,
• Zona (Dentro o Fuera ciudad),
• Fecha entrada,
• Hora entrada,
• Fecha salida,
• Hora salida,
• Duración,
• Tarifa,
• Pago.

Tip: en la columna Zona, crea una lista desplegable desde Datos > Validación de datos para elegir entre dos opciones (Dentro / Fuera). Así se evitan errores de tipeo que después rompen las fórmulas.

Paso 3: calcular la duración del viaje

La duración del viaje en la columna G, se calcula:

1. Con la fecha y hora de entrada en las columnas C y D,
2. y la fecha y hora de salida en las columnas E y F.

=((E2+F2)-(C2+D2))*24

¿Por qué se multiplica por 24?

Porque Excel guarda las fechas y horas como números de días: un día entero es el número 1. Entonces la resta te da la diferencia en días (por ejemplo, 0,1875 días), y al multiplicarla por 24 la convertís a horas.

Paso 4: asignar la tarifa según la zona

Podemos usar BUSCARV para asignar automáticamente la tarifa que corresponda, según lo que cargaste en la columna Zona. Es como pedirle a un asistente que revise la lista de TarifasZona y te traiga el número correcto, sin que vos tengas que buscarlo:

=BUSCARV(B2, TarifasZona!A:B, 2, FALSO)

¿Querés entender BUSCARV a fondo, con más ejemplos?

Ya le dedicamos una publicación entera: Todo sobre BUSCARV: la fórmula que te ahorra horas de trabajo.

Paso 5: calcular el pago según dos condiciones

Acá aplicamos las dos reglas del negocio.

Primera condición:

– Si el viaje duró 5 horas o menos, se paga a tarifa simple.

Segunda condición:

– Si duró más de 5 horas y terminó después de las 17, esas horas extra se pagan más caras, por estar fuera del horario habitual.

=SI(G2<=5, G2*H2, SI(F2>HORA(17,0,0), (5*H2)+((G2-5)*H2*1.5), (5*H2)+((G2-5)*H2*1.2)))

En lenguaje simple: hasta 5 horas, se paga hora normal. Pasadas las 5 horas, las horas de más se pagan con un 20% de plus si el viaja termina antes de las 17 hrs. y con un 50% si termina después.

Errores comunes al aplicar esto

• Cargar la hora como texto en vez de como hora real. Si Excel no la reconoce como hora, las fórmulas de resta van a fallar o dar resultados absurdos.

• Olvidar que un viaje puede cruzar la medianoche. En ese caso, la fecha de salida debe ser distinta a la de entrada, o la resta va a dar un número negativo.

• Mezclar la tabla de tarifas con la planilla de tareas. Mantenerlas separadas es lo que te permite cambiar una tarifa sin tocar todo lo demás.

Resumen

Con una tabla auxiliar de tarifas, una fórmula de duración y una condición anidada para el pago, resuelves en minutos algo que a mano lleva horas y da lugar a errores. El mismo esquema se puede adaptar a otros rubros con tarifas variables: delivery, técnicos, personal de guardia.

Si querés que armemos esta planilla a medida de tu negocio, con tus tarifas, tus zonas y tus horarios reales, escribinos y lo vemos juntos en una consultoría.

Archivo Utilizado