Sois muchos los que llegáis a este blog buscando una solución que os permita controlar la hora de entrada y salida de los empleados de la empresa. En concreto, muchos usuarios buscan una hoja de cálculo que les permita obtener tanto el número de horas normales de la jornada laboral (horas ordinarias), como el número de horas extras. Pues bien, atendiendo a estas solicitudes, he preparado una hoja de cálculo, que podréis descargar al final de este artículo, y que servirá de ejemplo. Espero que esta hoja de cálculo cubra todas nuestras necesidades, a la hora de calcular jornadas laborales ordinarias, horas extras, y remuneraciones por horas extraordinarias.
La hoja de cálculo no contiene macros, sólo contiene fórmulas de excel, y además no son nada complejas. Quizás algunas de las fórmulas sean un poquito farragosas, y requieran algo de tiempo para interpretarlas, pero una vez asimiladas, veréis que el tema no era tan complejo. Fijaos bien en el formato que tienen las celdas. Algunas de ellas tienen formato de fecha, numérico, y otras formato personalizado.
En esta hoja de cálculo, tenemos una serie de datos de entrada:
En la celda D4 introduciremos la hora de entrada.
En la celda D5, haremos lo mismo pero con la hora de salida.
En la celda D6 introduciremos el número de horas dedicadas a comer.
En la celda D7, y con una simple fórmula, obtendremos la jornada laboral ordinaria (sin contar horas extras). Si introdujéramos un dato manualmente, en esta celda D7, y fuera un dato inconsistente, en la celda D8 nos aparecería un mensaje informándonos que el número de horas ordinarias no es correcto.
En la celda D10, introduciremos el nombre del empleado.
En la celda D11, informaremos el precio de la hora extra, ya sea bruto, o neto. Dependiendo de si hemos introducido el importe bruto, o neto, la cifra total obtenida por las horas extras trabajadas, evidentemente atenderá también a ese mismo criterio, bruto o neto.
En esta hoja de cálculo, podemos observar también varias columnas:
La columna B y C, corresponden a la fecha y hora de entrada del empleado. Deberemos informar de ello diariamente, bien introduciendo el dato manualmente, o importándolo a través de algún otro programa (con un macro podríamos automatizar esta tarea).
La columna D y E, corresponden a la fecha y hora de salida del empleado. Deberemos informar también introduciendo el dato manualmente, o importándolo a través de algún otro programa.
La columna F nos informa de la cantidad de horas normales (horas dentro de la jornada laboral ordinaria), y es una fórmula.
La columna G nos informa de la cantidad de horas extraordinarias (las horas que exceden del horario laboral normal), y es una fórmula.
La columna H nos informa de las horas totales trabajadas (suma de horas normales, más horas extras). Se trata de una simple fórmula que suma las dos columnas anteriores.
La columna I nos informa de las horas y fracciones, en números decimales (ya sabes que por ejemplo 7:30, es decir, siete horas y treinta minutos, son en números decimales 7,5 horas). Nos bastará con multiplicar la columna inmediatamente anterior, por 24 (aunque también le hemos añadido la coletilla " h." en el formato de la celda).
La columna J nos informa del coste de las horas extraordinarias, siempre y cuando hayamos informado del precio por hora, en la celda D23. En la celda E23 informaremos de la moneda (en nuestro ejemplo, euros). Dependiendo del tipo de moneda elegido, la columna J nos mostrará el importe de las horas extras, expresado en la moneda elegida. La columna J nos muestra las cifras en formato texto, así que no intentéis sumar los datos de la columna, porque no obtendréis ningún resultado. Si deseáis que los importes siempre sean tratados como números, tan solo tendríais que multiplicar la columna F de las horas extras, por el coste de cada hora extra, que es el que figura en la celda D11.
Finalmente, en la celda E25 tendríamos el número de horas que el empleado no ha justificado, es decir, el número de horas que el empleado ha faltado (se ha ausentado), dentro del horario laboral normal de la empresa. Esta cifra la obtendremos comparando las horas de la columna F, con el número de horas ordinarias que aparece en la celda D7.
Aquí os dejo un pantallazo de la hoja de cálculo. Si deseáis ver la imagen más grande, tan solo tenéis que cliquear sobre de ella:
Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.
Control horario: Horas normales y horas extras
Suscribirse a:
Enviar comentarios (Atom)
52 comentarios:
Antes que nada felicitaciones. El contenido sobre excel es muy bueno y muy util. Mi consulta es la siguiente.
Necesito calcular cuanto tiempo tardaria una persona en realizar una tarea. Esta persona trabaja en un determinado horario, digamos de 08 a 16 hs, y se que segun metricas, puede obtener un determinado volumen de trabajo por hora. Lo que necesito es una formula o macro que me permita saber cuando va a terminar la tarea esta persona si se la asigno en un dia y hora especifica, teniendo en cuenta su horario de trabajo y contando los dias de lunes a viernes. Desde ya muchas gracias.
eb
TE QUERIA FELICITAR POR TU PLANILLA, ESTA MUY BUENA, PERO UNA PREGUNTA, QUE NO LA PUEDO SOLUCIONAR, SI UN EMPLEADO HACE HORAS EXTRAORDINARIAS (HORAS EXTRAS) COMO SABER CUANTAS SON AL 50% Y CUANTAS AL 100%, OSEA, APARTE DEL HORARIO DE TRABAJO, LAS HORAS AL 50% SON LAS QUE SE HACEN DENTRO DE LAS 06:00 AM HASTA LAS 22:0 PM, Y LAS HORAS AL 100% SON LAS QUE SE HACEN A PARTIR DE LAS 22:00 PM HASTA LAS 06:00 AM DEL OTRO DIA, DESDE YA MUCHAS GRACIAS, Y SI ME PODES AYUDAR MEJOR.....
Para casos particulares como el que planteas, deberás adapatar la plantilla. Piensa que cada empresa hace las cosas a su manera, y que esa plantilla es genérica. A partir de ahí, cada usuario debería aprovechar lo que le sirva, y cambiar el resto.
Un saludo.
Hola es excelente tu hoja de claculo pero tengo una inquietud y quisiera que tu me aydaras
en mi trabajao tenemos 1 hora de almuerzo que es de 13:00 a 14.00 pero en ocasiones regresas a las 14:10 o 14:30 eso significa un retrazo como calcularia tu hoja esta situacion con los minutos d erterazo del almuerzo .
y te felicito por tu trabajo es muy bueno, gracias
Atentamente
Miguel
Quito - ecuador
info@graficasandina.com
Alfonso: Muy buena la hoja de calculo, me gustaría poder sacar la cuenta de cuantas horas normales diurnas o nocturnas, como tambien para los festivos, estuve buscando la manera de hacer un control como este pero no pude con la sintaxis, muchas gracias se que me servirá de modelo.
como hago si yo quiero saber el valor de las horas pero en pesos colombianos, ya que le formato esta en euros???
Prueba a poner la palabra pesos en la celda E11
HOLA AYUDAME NO SE CUANTO DEBO PAGAR A UN EPMPLEADO QUE GANA 250 AL MES Y HACE 40 HORAS SUPLEMENTARIAS Y 7 HORAS EXTRAORDINARIAS..................REALEMNTE NO SE CUANTAS HORAS EFECTIVAMENTE SE TRABAJAN AL MES SI 176 O 160?????...............OTRA COSITA MAS ME PRODIAS CONFIRMAR CUANDO SON HORAS AL 100% Y HORAS AL 50%
REALMENTE SABIA LO SIGUIENTE:
DE 6:01 A 24:00 HORAS AL 50%
DE 24:01 AL 6:00 HORAS AL 100%???? CONTESTAME POR FAVOR LO MAS PRONTO POSIBLE..........gab_fri@hotmail.com
hola, me interesa mucho esta planilla, pero no puedo bajarla, me da error, la podra subir nuevamente, muchas gracias
Prueba de nuevo a descargarla, porque a mí me funciona. Debes introducir los 4 caracteres alfanuméricos que aparecen arriba a la derecha, para poder bajarte el fichero.
Un saludo.
Felicitaciones por tan importante publicaciones , he bajado alguna información que me han sido de mucha utilidad..... gracias.... saludos desde Veracruz, México
Esta planilla me ha resultado muy util,muchas gracias!! ahora bien, tengo un problema, he querido instalar la funcion "ahora()" pero se completa en la misma celda la fecha y la hora.. entonces se modifica toda la formula. Que me aconsejas para automatizar el ingreso de estos datos?
desde ya muchas gracias y saludos desde Uruguay!
Hola Karina.
Sitúate en la celda donde hayas puesto el AHORA(), y ves a continuación a Formato ---> Celdas ---> En la pestaña número, selecciona la categoría personalizada (si no te sale en primer plano), y verás como aparece esto:
dd/mm/aaaa h:mm
Si solo deseas que te aparezca la hora, elimina desde ahí mismo la primera parte (el "dd/mm/aaaa") y si lo que quieres es que solo te aparezca el día, pues elimina la parte final "h:mm"
Muchas gracias Javier!! Ahora necesito saber si esto es posible: a tu planilla le agregue dos botones para que marquen la hora de entrada y salida, pero si voy a las celdas (EJ:c15 o e15) las puedo modificar manualmente, yo quisiera que una vez que se marco la hora, esta no pueda ser cambiada. Probe con las opciones de proteger pero no me permite ejecutar el macro.. De nuevo muchas gracias por tu ayuda!!
Mírate este artículo donde se explica como proteger y desproteger con macros.
Tan solo tendrás que incluir tu código de los botones para poner la hora dentro de ese código del artículo. Es decir, en un mismo macro, desproteges la hoja, pones la hora, y vuelves a proteger la hoja.
perfecto, mil gracias por tu ayuda!!!!!!!!!
HOla, la planilla la he bajado, pero... COMO HAGO PARA MULTIPLICAR EQUIS PESOS POR LAS HORAS TRABAJADAS??, PORQUE LA RESPUESTA ME LA DA EN HORAS Y NO EN PESOS.
y ademas, (eso lo adapto yo calculo), sumarle al precio final semanal de horas trabajas (porque pago x semana trabajada), el viatico (que es un monto fijo)??? MUCHAS GRACIAS, DESDE YA.
SOY FANY DE ROSARIO, ARGENTINA
Hola, por sobre todo quiero felicitarte por esta ayuda, es valiosísima. Me apasiona el excel pero este es un desafío que siempre ha traído problemas.
Cómo multiplicar HORAS con DINERO para obtener un valor total, normalmente asociado a Horas Extras.
Pero, pese a todo lo bueno, sigo con problemas, y quisiera que me ayudes porfa:
TOTAL HORAS EXTRAS TRABAJADAS = 10:25 Hrs (10,42 Hrs)
VALOR HORA EXTRA = $ 4.278
Si lo hago con calculadora me dá: $44.574 (valor correcto)
Siguiendo la planilla, me dá un valor distinto: $ 44.563
Cuál es el error ?
Pues lo correcto, es lo que obtienes con esta plantilla, ya que realmente no solo son 10,42 h., sino que son 10,416666666 h., aunque en pantalla veas 10,42 h. (si sacas más decimales en pantalla, lo verás).
En realidad, si redondeas a 2 decimales el dato numérico, para que te de 10,42 h. exactas, el valor que obtendrás será $ 44,57676. Un dato que como ves, se acerca más al de la calculadora, aunque he de decirte que el de la calculadora no es el correcto, sino el que obtienes de la plantilla.
Todo depende de si quieres redondear la cifra decimal a 2 decimales, o no.
Un saludo.
Hola muy buena la hoja de excel,pero tengo un problema:como reflejar en la hoja que en vez de horas extras se han hecho menos minutos de los que se exigen por convenio ya que al ser minutos negativos aparece con almohadillas si lo pongo en formato horas y si lo pongo en formato numero aparece 0, en vez de -4 minutos que es lo que debiera aparecer.ES urgente.Gracias
Excel no puede trabajar con minutos negativos, porque no existe la máquina del tiempo ;-)
Imagina que tienes esto en las celdas que te indico a continuación:
A1 = 13:22
B1 = 13:18
Si en C1 pones esto:
=B1-A1
Obtendrás las famosas almohadillas. Para solucionarlo, prueba a cambiar el sistema de fechas a 1904: Herramientas Opciones Pestaña Calcular Muesca en Sistema de fechas 1904.
Una vez hecho eso, pon esta fórmula en C1:
=TEXTO(ABS(B1-A1);"-hh:mm")
De esa forma, ya tendrás el dato que buscabas: -00:04
Eso sí, no podrás operar con esos datos, pues se trata de un texto, dado que Excel, no considera formato horario, las horas negativas.
Saludos.
muchas gracias por la ayuda
queria preguntarte como puedo formular para que me aparexcas las horas totales sin diferenciar horas extras ni de descanso, lo intente y no lo logro
gracias
Si te fijas, hay una columna de un color más intenso, que precisamente tiene el total de horas (extras + normales).
Un saludo.
Excelente la hoja,pero tengo un inconveniente que no lo puedo solucionar: mi trabajo no tiene horario fijo, o sea que todos los días es diferente el horario y si desde las 22 hs. a las 6 hs, se considera hora extra, y ese día fue de 16 hs a 24 hs, hay alguna fórmula que separe las horas en comunes o extras, o tengo que agregar alguna columna para las extras y cargarlas por separado?. Desde ya muchas gracias
Lamentablemente no existen fórmulas para el control de horas laborables y horas extras, así que tendrás que adaptar una solución a tus necesidades, siguiendo el ejemplo estandar que colgué.
Con todas las fórmulas que hay en ese ejemplo, podrás montar lo que necesitas.
Un saludo.
hola soy nuevo y quisiera una aclaracion sobre un problema en una hoja de excel
necesitos cambios en su planilla en la semana son 45 hrs y el pago es en pesos chilenos
hola soy marcelo muy buena la planilla, yo tenia otra pero vi que esta era mejor y la cambie gracias!!! ahora pregunta:
como convierto las horas en pesos
B2= $1.520 Y C4= 05:51:22 HRS
ME AYUDAS PLIS
Prueba así:
=(C4*24)*B2
Dale formato de número a la celda donde pongas esa fórmula.
Todo esto está en la misma planilla que te has descargado. Solo hay que echarle un vistazo a las fórmulas, para verlo.
Saludos
gente tengo que calcular horas turno tarde mañana y noche, alguien me puede ayudar. facuescofer@yahoo.com.ar
Antes de nada, felicidades... una página, cojonuda!!
estoy intentado hacer un listado formulando la hora y fecha en la que introduzco un dato en una celda... es decir, por ejemplo, en la columna A4 tengo mi fórmula: =SI(B4="";"";AHORA())
para que cuando ponga un dato en B4, me grabe la fecha y la hora en la que ese dato fué introducido, con el sgte. formato:
dd/mmm - h:mm AM/PM
pero a medida que sigo introduciendo datos en las columnas posteriores, me actualiza todas las fórmulas, poniéndome la hora en la que introduzco el último dato.
Mi pregunta es:
¿Cómo puedo hacer para que, automáticamente me ponga la fecha y la hora en la que introduzco un dato en otra celda, sin que me cambie toda la formulación anterior??.
No se si me he sabido explicar, pero de antemano, gracias por vuestra respuesta!
Best Regards,
Se me ocurre que podrías hacerlo con un macro. Utiliza este a ver si te resuelve el asunto (debes colocarlo en la hoja en cuestión, no en un módulo). Se ejecutará cada vez que cambies algún dato de la hoja:
Private Sub Worksheet_Change(ByVal Target As Range)
'si hay errores, que continúe
On Error Resume Next
'si cambiamos un dato, ponemos la fórmula a la izquierda
If ActiveCell <> "" Then
ActiveCell.Offset(0, -1).Select
ActiveCell = Now()
Selection.NumberFormat = "dd/mmm - h:mm AM/PM"
End If
End Sub
Saludos.
hola!!! lo queria saber es como hago si yo trabajo los sabados de 08:00 a 13:00hs, si se pueden sumar como horas normales... y si me paso de las 13:00, como horas extras... no se si me explique bien..
saludos y agradezco su ayuda y tambien la planilla...
Bájate el ejemplo que hay al final del artículo, y verás como ese ejemplo, puedes aplicarlo a tu caso concreto perfectamente.
Un saludo.
GRACIAS POR LA PLANILLA, EXCELENTE UN SALUDO
hola muy buenas, pregunta...y si las horas extras las haces en las horas de la comida, quiero decir si te paras solo media hora para comer, entrando a las 8:00 de la mañana parar a la 13:00 y empezar de nuevo a la 13:30 hasta las 18:00 pensando que tienes 2 horas de descanso, asi que harias
5:00 por la mañana
4:30 por la tarde
y asi hacer 1:30 de horas extras
como lo resolveria?????
Si te fijas, en la plantilla solo se informa de la hora de entrada por la mañana y salida por la tarde.
En tu ejemplo, una de dos, o montamos una hoja similar, donde se informe de la hora de entrada por la mañana, de la hora de salida, para comer, de la hora de entrada después de comer, y de la hora de salida por la tarde, o bien seguimos trabajando con esa plantilla, y considerando que en lugar de haber entrado a las 13:30, ha salido a las 19:30. De esta última forma, se computarían 1:30 h. extras.
Si son cosas puntuales, yo te recomendaría usar este segundo ejemplo, pues el primero te requiere de la introducción del doble de datos (salida para comer, y entrada después de comer).
Un saludo.
Javier, Felicitaciones por la ayuda, tengo reloj control que registra el tiempo tomado en colacion, he tratado de insertar estos campos salida a colación entrada de colacion en la planilla pero no me resulta. todo lo demas esta como anillo al dedo.. jakaja gracias por tu ayuda.
Ronald Aguirre
Chile
Hola muy buen blog dedicado al excel felicidades,
tengo un problema con las horas extras, en mi pais las extras de lunes a sabado se pagan doble, y los dias domingos triple.
Ejemplo: por cada hora que trabajas es como si trabajaras dos horas,
en domingo por cada hora que trabajas es como si trabajaran tres.
el extra hora de calcula: total ganado/26dias/8horas=horas/dia.
como hago una formula para que reconosta el domingo y tripleque las horas. saludos
Hola Dr.House. Si en A1 tienes puesta la fecha, por ejemplo en formato dd-mm-aa, pon esto en una celda contígua, para obtener el día de la semana (domingo=1, lunes=2, martes=3, miércoles=4, jueves=5, viernes=6, sábado=7):
=DIASEM(A1)
Por ejemplo, si en A1 tienes puesta la fecha: 14-03-2011
En la celda donde tengas la fórmula =DIASEM(A1) te aparecerá un 2, que es el número correspondiente al día de la semana (martes).
Ahora si quieres multiplicar el importe de los domingos por 3, solo tienes que construir un condicional SI, de una forma parecida a esta:
=SI(DIASEM(A1)=1;preciohora*numerodehoras*3;C1)
Donde “preciohora” y “numerodehoras” son las celdas donde tienes precisamente esos datos, el precio de cada hora, y el número de horas trabajadas ese día.
Saludos.
Hola! mil gracias por la ayuda y el tiempo! si pudieras ayudarme con lo siguiente seria una salvada: donde trabajo todas las personas tienen horarios diferentes, entonces necesito contabilizar la hora de entrada y salida en funcion de un horario diario establecido que al final de la semana me tiene ke dar equis horas semanales, entonces si un dia una persona entra a las 7 y otro a las 8 pero en total no se pasa del limite no le debo ni contar ni descontar horas extras por que realmente no esta trabajando horario extraordinario. Por ejemplo la persona trabaja 48 hrs semanales en 6 dias en los que un dia va de 6 am a 2 pm y otro de 7 am a 3 pm.
Hola Javier, gracias por tu documento, me ha servido mucho ;) !!! en el trabajo que estoy tenemos horarios rotativos, así que he creado uno para cada turno.. pero en el que es de madrugada (entrada 20H00 - salida 08H00) no me permite la fórmula; probablemente sea algo ridículamente bobo, pero no puedo encontrar el error.... me podrías ayudar?
Te tiene que funcionar igualmente. Solo tienes que informar que el día de entrada es uno, y el día de salida es otro. En esa plantilla ya se contempla la posibilidad de que esto ocurra, porque si te fijas, aparece el día de la entrada y el día de salida, aparte de la hora de entrada y salida.
En este otro artículo tienes más info al respecto: trabajando con horas, minutos, y segundos.
Saludos.
me parece excelente la hoja de calculo, de verda que te felicito, de mucha ayuda
Tengo un negocio que pago por horas trabajadas y tu hoja ha sido de gran ayuda, pero necesito ver la posibilidad que las "horas trabajadas" y las "horas y fracciones" sean el mismo monto. Ya que al sumar por cada uno de los trabajadores la diferencia entre ambas se incrementa
Esperando me puedas ayudar
Gracias
Alberto
Hola Alberto.
No pueden coincidir en la vida, pues por ejemplo 7:30 (siete horas y treinta minutos), en números decimales son 7,50 (siete horas y media).
Un saludo.
Mis felicitaciones por el blog, por la plantilla y por la paciencia de Javier.
Gracias por este control de horario la verdad me sirvió mucho, el contenido es muy bueno y gracias a su control me pude guiar para crear el control para hacer mis practicas, una vez mas gracias y felicidades por un buen trabajo saludos de mi parte amigo.
primero que nada felicitarte por el excelente trabajo ya que es de gran ayuda .. pero mi consulta es otra necesito una para todo lo contrario osea contar atrasos y me diga tiempo de atrasos .. quiziera saber si me puedes ayudar por favor ?
Jose Gonzalez, si analizas bien esa hoja de cálculo te darás cuenta que las fórmulas y los formatos de celda, te sirven tanto para controlar puntualidad de empleados, como retrasos.
Solo hay que dedicarle un ratito de estudio.
Un saludo.
HOLA La plantilla es de mucha utilidad pero si de refrigerio solo tenemos 45 minutos como puedo ponerlo???
Uf, esta hoja de cálculo la uso en la oficina desde el 2009 y me ha sido muy útil. Sigo revisando las macros en este emocionante mundo de la programación y me parece de justicia agradecer por el gran gran aporte que nos das con tus trabajos. Muchas gracias!!
Publicar un comentario