#controldeasistencia #controldeasistenciaenexcel #controldeasistenciadepersonal #controldeasistenciadepersonalexcel #listadeasistenciaexcel #listadeasistencialaboral #basededatosexcel #sancionesporinasistencialaboral #multasdetrabajo #justificaciondefaltas #justificaciondefaltasportrabajo
Hola emprendedores y entusiastas productivos. En este artículo vamos a realizar un formulario para el registro de asistencia de personal con Excel y VBA. Por medio de este formulario se podrá registrar la asistencia de la nómina de trabajo, la misma podría estar relacionada de manera automática con las multas que incumplan los trabajadores por incurrir en atrasos, además estar asociada a una base de datos de justificaciones por faltas de un trabajador en un periodo dado; la asistencia refleja los días reales laborados por cada empleado, lo que se contabiliza en el rol de pagos, mismo que forma parte de los costos indirectos de fabricación.
Bien, en este artículo se tratará 3 temas íntimamente relacionados a la asistencia de personal y son los siguientes:
* Formulario de registro de asistencia de personal.
* Código añadido al registro de asistencia para determinar multas automáticamente.
* Formulario para la relación de la asistencia con una base de datos de justificaciones.
En el mercado existen relojes de asistencia biométricos los cuales serían la primera opción para hacer el registro automático del personal, pues desde los mismos se pueden enviar los datos a libros de Excel; si este no se pudiera conseguir por A o B motivo, se puede optar por una opción semiautomática más económica, por Excel y VBA.
Así entonces, vamos con el procedimiento para el desarrollo del reloj de asistencia.
1. Base de datos de nómina y claves de acceso
Se debe tener la plantilla de nombres que forman parte del proyecto o de la compañía, estos pueden ser asignados como rangos dinámicos y ser asociados a un nombre. Una ilustración que muestra una forma en que deben introducirse los datos de nómina es la siguiente:
Si deseas observar detalladamente el esquema del formulario y el código antes mostrado puedes ir al siguiente enlace:Base de datos en Excel para nómina.
A la vez que se crea la base de datos para nómina, se debe colocar el password o clave de acceso que tendrá el personal para el registro de su asistencia.
2. Creación de formulario
Vamos ahora con la elaboración de un formulario para asignar el registro de asistencia, como se observa en la gráfica siguiente:
La elaboración del diseño de un formulario en Vissual Basic es bastante sencillo, basta con seguir los pasos a continuación:
- Abrir Excel.
- Abrir Vissual Basic (Alt+F11) o en su defecto, click en la pestaña programador y click en Vissual Basic. Si no lo tienes activado, vamos a archivo, personalizar cinta de opciones, en la parte pestañas principal seleccionar Desarrollador, click en agregar y aceptar.
- En el entorno Vissual Basic (VBA) click en la pestaña ver, click en explorador de proyectos si no se tiene activado.
- Click en la pestaña Insertar, User Form.
- Click en la pestaña ver, ventana propiedades, en el caso de tenerla abierta.
- Click en la pestaña ver, cuadro de herramientas, en caso de no tenerla abierta.
- Modificar las esquinas del User Form abierto, para hacer más grande o más pequeño el espacio del formulario.
- Con el cuadro de herramientas activo, añadir los diferentes elementos para disponer un formulario personalizado como el mostrado anteriormente (si pasamos el cursor por las herramientas nos muestra el nombre de la herramienta).
Vamos a ver qué hacen los diferentes campos del cuadro de herramienta en el formulario anterior:
* Label 1 o Etiqueta 1: Muestra la fecha actual.
* Label 2 o Etiqueta 2: Muestra la hora actual.
* Label 3 o Etiqueta 3: Muestra el nombre de la persona a quien corresponde el password.
* Label 4 o Etiqueta 4: Muestra el cargo de la persona.
* Text box o caja de texto: Muestra las horas que han sido asignadas por el trabajador(a) y no pueden ser modificadas.
* Button o Botón: Registra las diferentes horas del día.
* Frame o Marco: Asocia los diferentes campos para que actúen en conjunto y no de forma aislada.
Esta es una forma de realizar un formulario para registro de asistencia, los diferentes usuarios podrán personalizarlo a sus necesidades.
3. Codificación VBA
Los formularios deben contener controladores de error para guiar al usuario y generar una correcta entrada de datos. Los casilleros de horas deben estar bloqueados contra escritura, y serán de lectura solamente, pues aquí se mostrará la hora real de los diferentes tiempos. Los diferentes códigos se anotan con diferentes eventos dentro del formulario. Para ir dentro del formulario y colocar el código completo asociado al mismo basta con hacer click derecho en el formulario o UserForm y click en ver código. Bien, observemos parte del código que contendrá este formulario, específicamente cuando se necesite añadir el registro de asistencia para la hora de entrada, y el tiempo señalado en las etiquetas 1 y 2:
Añadir Tiempo real a las etiquetas 1 y 2:
Public exitTime As Double
Private Sub UserForm_Initialize()
'Formato de la fecha y hora
Label1.Caption = Time
Label1.FontSize = 16
Label1.ForeColor = vbBlack
'Optimizar la ejecución de la macro
Application.ScreenUpdating = False
'Nombre del formulario o UserForm: Reloj Marcador
RELOJMARCADOR.Label1.Caption = Time
'Variable de salida
exitTime = Now + TimeValue("00:00:01")
Application.OnTime exitTime, "reloj"
Label2.Caption = Format(Now, "dd/mm/yyyy")
Label2.FontSize = 16
Label2.ForeColor = vbBlack
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Función para el reloj marcador
If CloseMode = 0 Then
exitTime = Now + TimeValue("00:00:01")
Application.OnTime exitTime, "reloj"
Application.OnTime exitTime, "reloj", , False
End If
End Sub
Los nombres de las etiquetas, textbox o formularios se lo modifica en la ventana de propiedades en la parte (Name), las mismas se encuentran de color café en el código anterior.
Registro de la Hora de entrada
Private Sub CommandButton1_Click() 'Botón de la hora de ingreso
' Control de error : Si no hay usuario a quien buscar
If nombre.Caption = "" Then
MsgBox ("No existe un nombre para registrar la hora de de ingreso"), vbCritical
Exit Sub
End If
'Colocar la hora
hingreso.Text = TimeValue(Now)
hingreso.FontSize = 14
'Guardar la hora de ingreso
Sheets("BD").Select 'Hoja del libro
For i = 7 To 1000000 'Número de Fila
If Cells(i, 84).Value = "" Then 'Número de Columna
Cells(i, 84).Value = Format(Now, "mm/dd/yyyy")
Cells(i, 85).Value = nombre.Caption
Cells(i, 86).Value = Label9.Caption
Cells(i, 87).Value = TimeValue(Now)
Exit For
End If
Next
MsgBox ("Hora de ingreso almacenada" & vbNewLine & "Bienvenido(a) " & nombre.Caption), vbInformation
End sub
Así mismo los casilleros y etiquetas pueden reemplazarse por otros nombres; debemos recordar que los formularios deben contener otros controladores de error para evitar una incorrecta entrada de datos. Los otros botones de hora de break, hora del fin del break y hora de salida pueden tener un código similar, pero sin olvidar de colocar controladores de error, que indudablemente representan una guía del usuario para una correcta entrada de datos.
4. Establecer política por multas
La organización puede establecer multas por atrasos, los cuales si no son justificados se podrían reflejar en el rol de pagos del personal, siempre y cuando se decida ejecutar este tipo de políticas. Si este es el caso se podría primero asignar las variables que entran en este tipo de táctica. Observemos la siguiente tabla:
Los espacios en gris son aquellas variables que se pueden modificar, pero siempre manteniendo el mismo formato, pues si vamos a relacionarlo con una fórmula integrada a una macro esta no podrá ejecutarse de igual forma si su formato varía.
Esta tabla nos dice lo siguiente:
La hora de entrada es a las 8:00 AM y la salida es a las 14:00 PM. El tiempo de atraso máximo en la hora de entrada es de 5 minutos. Es decir, si la persona llega a las 8:05:00 minutos, esta persona llegará tarde e incurrirá en 4 tipos de atraso:
* Si llega hasta con 30 minutos de atraso, la multa será del 30% sobre su sueldo diario.
* Si llega de 30 minutos a 1 hora de atraso, la multa será del 40% sobre su sueldo diario.
* Si llega de 1 hora a 2 horas de atraso, la multa será del 50% sobre su sueldo diario.
* Si llega con más de 2 horas de atraso, la multa será del 100% sobre su sueldo diario.
Asimismo, la tabla indica que el tiempo de descanso (break) es de 1 hora, si la persona llega con más de 1 hora de descanso podría incurrir 3 tipos de multas:
* Si llega hasta con 15 minutos de atraso, la multa será del 30% sobre su sueldo diario.
* Si llega de 16 minutos a 30 minutos de atraso, la multa será del 50% sobre su sueldo diario.
* Si llega con más de 30 minutos de atraso, la multa será del 85% sobre su sueldo diario.
Ahora bien, tenemos que el sueldo diario es el simple cálculo del sueldo base dividido para el periodo de cálculo, en este caso, 30 días.
Como se puede observar, existen varias combinaciones en que la multa exceda el 100%, esto podría modificarse para no verse como "rajatabla", o en su defecto, verse como una manera de mejorar la puntualidad; esto ya es una forma de perspectiva y por lo tanto depende enteramente de la administración.
5. Asignar fórmulas para asociar variables
Como se ha observado, hay dos tipos de multas en los atrasos, para la hora de entrada y para la hora de descanso. Por lo tanto, hay dos fórmulas para asociar las variables anteriormente vistas.
Multa entrada
Podemos realizar una forma anidada como la presente u otra que el usuario observe.
Notas:
* Las políticas anteriormente observadas se pueden encontrar en una hoja del libro de cálculo, en este caso se llama "PLANDECUENTAS". Las variables se denotan en las celdas que se observan a continuación:
* La hora de entrada, se asigna en una celda, en este caso la GP7.
* El nombre de la persona se asigna en una celda, en este caso la GN7.
* El sueldo base de la persona, está en el rango de búsqueda de la nómina de personal BD!$BD$7:$CA$1000; posición en la columna 24.
Con estas premisas, se presenta una forma de anidar la información en una fórmula:
=SI.ERROR(SI(Y(GP7-PLANDECUENTAS!$EN$3>PLANDECUENTAS!$EN$13;GP7-PLANDECUENTAS!$EN$3<=PLANDECUENTAS!$EN$5);(BUSCARV(BD!GN7;BD!$BD$7:$CA$1000;24;FALSO)/PLANDECUENTAS!$EN$14)*PLANDECUENTAS!$EO$5;SI(Y(GP7-PLANDECUENTAS!$EN$3<=PLANDECUENTAS!$EN$6;GP7-PLANDECUENTAS!$EN$3>PLANDECUENTAS!$EN$5);(BUSCARV(BD!GN7;BD!$BD$7:$CA$1000;24;FALSO)/PLANDECUENTAS!$EN$14)*PLANDECUENTAS!$EO$6;SI(Y(GP7-PLANDECUENTAS!$EN$3>PLANDECUENTAS!$EN$6;GP7-PLANDECUENTAS!$EN$3<=PLANDECUENTAS!$EN$7);(BUSCARV(BD!GN7;BD!$BD$7:$CA$1000;24;FALSO)/PLANDECUENTAS!$EN$14)*PLANDECUENTAS!$EO$7;SI(GP7-PLANDECUENTAS!$EN$3>PLANDECUENTAS!$EN$7;(BUSCARV(BD!GN7;BD!$BD$7:$CA$1000;24;FALSO)/PLANDECUENTAS!$EN$14)*PLANDECUENTAS!$EO$8;0))));"HAY ERROR")
Las variables de la política por multas se presenta con color café.
Multa por hora de descanso
Las premisas son exactamente las mismas, excepto para GQ7 Y GR7, las cuales representan la hora inicial de descanso, y la hora final del descanso respectivamente. Con ello, se presenta la fórmula como sigue:
=SI.ERROR(SI(Y(GR7-GQ7>PLANDECUENTAS!$EN$12;GR7-GQ7<=PLANDECUENTAS!$EN$9);(BUSCARV(BD!GN7;BD!$BD$7:$CA$1000;24;FALSO)/PLANDECUENTAS!$EN$14)*PLANDECUENTAS!$EO$9;SI(Y(GR7-GQ7>PLANDECUENTAS!$EN$9;GR7-GQ7<=PLANDECUENTAS!$EN$10);(BUSCARV(BD!GN7;BD!$BD$7:$CA$1000;24;FALSO)/PLANDECUENTAS!$EN$14)*PLANDECUENTAS!$EO$10;SI(GR7-GQ7>PLANDECUENTAS!$EN$10;(BUSCARV(BD!GN7;BD!$BD$7:$CA$1000;24;FALSO)/PLANDECUENTAS!$EN$14)*PLANDECUENTAS!$EO$11;0)));"HAY ERROR")
6. Asociar fórmulas a código VBA
Una vez desarrolladas las fórmulas, es momento de transformarlas a código y asignarlas a un evento, como puede ser al botón hora de salida del formulario para control de asistencia creado con anterioridad.
'Fórmula multa entrada
Range("GU" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(AND(RC[-5]-PLANDECUENTAS!R3C144>PLANDECUENTAS!R13C144,RC[-5]-PLANDECUENTAS!R3C144<=PLANDECUENTAS!R5C144),(VLOOKUP(BD!RC[-7],BD!R7C56:R1000C79,24,FALSE)/PLANDECUENTAS!R14C144)*PLANDECUENTAS!R5C145,IF(AND(RC[-5]-PLANDECUENTAS!R3C144<=PLANDECUENTAS!R6C144,RC[-5]-PLANDECUENTAS!R3C144>PLANDECUENTAS!R5C144),(VLOOKUP(BD!RC[-7],BD!R7C56:R1000C79,24,FALSE)/PLANDE" & _
"CUENTAS!R14C144)*PLANDECUENTAS!R6C145,IF(AND(RC[-5]-PLANDECUENTAS!R3C144>PLANDECUENTAS!R6C144,RC[-5]-PLANDECUENTAS!R3C144<=PLANDECUENTAS!R7C144),(VLOOKUP(BD!RC[-7],BD!R7C56:R1000C79,24,FALSE)/PLANDECUENTAS!R14C144)*PLANDECUENTAS!R7C145,IF(RC[-5]-PLANDECUENTAS!R3C144>PLANDECUENTAS!R7C144,(VLOOKUP(BD!RC[-7],BD!R7C56:R1000C79,24,FALSE)/PLANDECUENTAS!R14C144)*PLANDECUEN" & _
"TAS!R8C145,0)))),""HAY ERROR"")"
'Fórmula multa break
Range("GV" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(AND(RC[-4]-RC[-5]>PLANDECUENTAS!R12C144,RC[-4]-RC[-5]<=PLANDECUENTAS!R9C144),(VLOOKUP(BD!RC[-8],BD!R7C56:R1000C79,24,FALSE)/PLANDECUENTAS!R14C144)*PLANDECUENTAS!R9C145,IF(AND(RC[-4]-RC[-5]>PLANDECUENTAS!R9C144,RC[-4]-RC[-5]<=PLANDECUENTAS!R10C144),(VLOOKUP(BD!RC[-8],BD!R7C56:R1000C79,24,FALSE)/PLANDECUENTAS!R14C144)*PLANDECUENTAS!R10C145,IF(RC[-4]-RC[-5]" & _
">PLANDECUENTAS!R10C144,(VLOOKUP(BD!RC[-8],BD!R7C56:R1000C79,24,FALSE)/PLANDECUENTAS!R14C144)*PLANDECUENTAS!R11C145,0))),""HAY ERROR"")"
Nota:
Para pasar a código VBA una fórmula basta con copiar la fórmula desarrollada con Ctrl + C, borrarla de la celda con supr, ir a la pestaña desarrollador, grabar macros, asignar un nombre a la macro, ir a la celda, desplegar la barra de fórmula y pegarla en ese sitio con Ctrl + V, dar enter, y detener la grabación de la macro. Después de ello, se debería depurar el código, puesto la mayoría de las veces las grabaciones tienen código innecesario que se puede eliminar.
Si deseas visualizar la ejecución del programa para el proceso de multas puedes observar el siguiente vídeo:
7. Elaborar formulario de "Justificaciones" para guardar las variables en una base de datos
Vamos ahora con la elaboración de un formulario en el entorno VBA para asignar justificaciones de los días de falta incurridos por los trabajadores, como se observa en la gráfica siguiente:
Los formularios o formas son bastante fáciles e intuitivas de realizar, sin embargo, si requieres ayuda para el desarrollo de su construcción puedes entrar al siguiente enlace: Construcción de formularios en Excel y VBA, el detalle se encuentra en el punto 3 de este post.
8. Codificación VBA
Los formularios deben contener controladores de error para guiar al usuario y generar una correcta entrada de datos. El casillero de Fecha puede estar bloqueado contra escritura si se va a utilizar un calendario emergente, o estar envuelta en código en el evento change para colocar un único formato de fecha. Los comboBox o listas desplegables que muestra el formulario se pueden asociar a un rango dinámico. Si aún no sabes como hacerlo, entra al siguiente enlace: Rangos dinámicos en Formularios.
Los diferentes códigos se anotan con diferentes eventos dentro del formulario. Para ir dentro del formulario y colocar el código completo asociado al mismo basta con hacer click derecho en el formulario o UserForm y click en ver código. Bien, observemos parte del código que contendrá este formulario, específicamente cuando se necesite añadir el registro de justificaciones en base a la base de datos de multas:
Nota adicional del código:
Una forma de verificar si es necesario añadir justificaciones del tipo " multas por atrasos" es mediante el uso de las siguientes variables, las cuales se aplican al código posteriormente:
- Multa por atraso 1: Sheets("PLANDECUENTAS").Range("CK3").Value
- Multa por atraso 2: Sheets("PLANDECUENTAS").Range("CK4").Value
- Fecha del atraso en la BD de atrasos: Sheets("BD").Columns(195)
- Nombre de la persona que incurrió en el atraso: Sheets("BD").Columns(196)
'Controlador de error: vacíos
If TextBox1.Text = "" Or ComboBox1.Text = "" Or ComboBox2.Text = "" Or TextBox2.Text = "" Then
MsgBox ("Faltan campos por rellenar"), vbCritical
Exit Sub
End If
'Controlador de error 2: Datos no coinciden para justificar multas por atrasos
If ComboBox2.Text = Sheets("PLANDECUENTAS").Range("CK3").Value Or ComboBox2.Text = Sheets("PLANDECUENTAS").Range("CK4").Value Then
Registro = Format(TextBox1.Text, "mm/dd/yyyy")
Registro2 = ComboBox1.Text
contarduplicado = Application.WorksheetFunction.CountIfs(Sheets("BD").Columns(195), Registro, Sheets("BD").Columns(196), Registro2)
If contarduplicado = 0 Then ' No coinciden
MsgBox ("Datos no coinciden." & vbNewLine & "No existen multas para esta persona en éste día"), vbCritical
Exit Sub
End If
End If
'Optimizar la ejecución de la macro
Application.ScreenUpdating = False
'Colocar los datos para justificaciones
Sheets("BD").Select
For i = 7 To 100000
If Cells(i, 137).Value = "" Then
Cells(i, 137).Value = Format(TextBox1.Text, "mm/dd/yyyy")
Cells(i, 138).Value = ComboBox1.Text
Cells(i, 140).Value = ComboBox2.Text
Cells(i, 143).Value = TextBox2.Text
Exit For
End If
Next
MsgBox ("Datos sobre justificaciones almacenados con éxito"), vbInformation
End Sub
Parte del código que se encuentra de color café representan las variables a modificar por parte del usuario si quiere mantener el código funcionando exactamente como se muestra en el ejemplo.
Si deseas visualizar la ejecución del programa para el formulario de justificaciones puedes observar el siguiente vídeo:
Eso sería el procedimiento para tener un control de asistencia de personal con Excel y VBA. Si deseas realizar tu propio reloj marcador para el registro de asistencia de personal, multas y justificaciones puedes considerar adquirir nuestro sistema a un costo bastante accesible, el cual muestra todo el código empleado para los formularios en cuestión con un único pago y licencia perpetua, el mismo te permite editar todo el código sin ningún tipo de restricción, y muchos otros beneficios y funcionalidades que puedes observar en esta web en el apartado Descripción - Funciones.
Si deseas visualizar la ejecución del programa para el formulario del control de asistencia puedes observar el siguiente vídeo:
Por mi parte, me despido, sin antes desearles un genial día. Y, no se olviden de sonreír, para recordar que la vida sonríe siempre.