Calcular horas laborales entre dos horas considerando horario de receso

Como complemento del artículo anterior, donde se muestra como calcular la cantidad de horas laborales entre dos horas de misma o diferentes fechas, se incluye una mejora en la que contempla un receso en el medio de la jornada laboral.

Partiendo del proceso ya mostrado los cambios para no incluir en el resultado final el tiempo del descanso intermedio son los siguientes:

Para el caso en que el inicio y fin de la tarea se den en el mismo día, se deberá identificar si existe intersección en el horario de la tarea y el horario del receso, si existe, al resultado final se le deberá restar el tiempo transcurrido desde la hora mayor entre la hora de inicio de la tarea y la de hora de inicio del receso hasta la hora menor entre el final de la tarea y el final del receso.

De lo contrario, cuando la hora de inicio y fin se den en distintos días, se deberán realizar 3 cálculos adicionales:

1. Si la hora de inicio de la tarea es menor o igual a la hora de inicio del período de descanso, al resultado final se le deberá restar el tiempo transcurrido desde la hora mayor entre el inicio de la tarea y el inicio del descanso hasta el final del descanso.

2. Para los días laborales completos intermedios, la duración de la jornada laboral se deberá modificar de manera que no incluya el período de descanso (en el ejemplo anterior eran 10 horas de trabajo, si el descanso es de 1 hora, en este nuevo ejemplo serán 9 horas laborales).

3. Si la hora de cierre de la tarea es mayor o igual a la hora de fin del período de descanso, al resultado final se le deberá restar el tiempo transcurrido desde la hora del fin del descanso hasta la hora menor entre el cierre de la tarea y el fin del descanso y el final del descanso.

La fórmula en Microsoft Excel para el cálculo de las horas laborales entre dos horas considerando tiempo de receso es la siguiente:

Las referencias utilizadas contienen:
A2 y B2: Fecha y hora del inicio de la tarea.
C2 y D2: Fecha y hora del final de la tarea.
HH_INI: Hora de inicio de la jornada laboral
HH_FIN: Hora de fin de la jornada laboral.
HH_JOR: Duración de la jornada laboral.
HH_RES_I: Hora de inicio del receso intermedio.
HH_RES_F: Hora de fin del receso intermedio.
DIAS_FESTIVOS: Lista de días festivos no laborables.

El fichero con el ejemplo actualizado se encuentra aquí.

Calcular horas laborales entre dos horas de igual o distintas fechas

En su momento se mostró como calcular la cantidad de días laborales entre dos fechas, ahora se mostrará como calcular las horas laborables entre dos horas de la misma o distintas fechas.

Por ejemplo si la jornada laboral es de lunes a viernes de 9:00 hs a las 19:00 hs, llega una petición a las 10:00 hs y se resuelve a las 14:00 hs el plazo de atención es de 4 horas. Si la solicitud del trabajo llega a las 18:00 hs de un viernes y se finaliza a las 10:00 hs del lunes siguiente la misma ha durado solo 2:00 hs de duración.

Para complicar un poco más la situación se permite el ingreso de las peticiones antes de la hora laboral, y el cierre después de la misma (aunque esta última parte no tiene mucho sentido).

Como caso particular, si la solicitud y la resolución del trabajo se realizó el mismo día, la duración se calculará como el tiempo transcurrido entre las siguientes horas:

HoraFin = Hora mínima entre la hora final de la jornada laboral y la hora de cierre del trabajo.
HoraInicio =Hora máxima entre la hora inicial de la jornada laboral y la hora de solicitud del trabajo.
Tiempo = HoraFin - HoraInicio

Y para el caso de que se resolvió en otra fecha, la siguiente parte del problema se separará en tres:

1. Tiempo transcurrido desde la máxima hora entre la solicitud del trabajo y el inicio de la jornada laboral, hasta el final de la jornada laboral. Esto aplica solo si el día es laborable, y la petición llegó dentro de la jornada laboral.

2. Cantidad de días laborables entre el siguiente día en que se recibió la solicitud del trabajo y el día anterior al que se finalizó multiplicado por la cantidad de horas de la jornada laboral.

3. Tiempo transcurrido desde el inicio de la jornada laboral hasta la hora mínima entre el cierre del trabajo y el final de la jornada laboral. Esto aplica solo si el día es laborable, y la petición se cerró dentro de la jornada laboral.

Formula para el cálculo de horas laborales con Microsoft Excel:

Suponiendo que en A2 y B2 se encuentran la fecha y hora de inicio respectivamente, y en C2 y D2 la fecha y hora de fin respectivamente, la formula, un poco compleja, es la siguiente:

Se recomienda ver el uso de la función DIAS.LAB (la cual se debe de activar).

La función HORANUMERO convierte una hora en formato de texto a un tipo de dato fecha/hora.

Como versión mejorada se pueden definir celdas para la hora de inicio de la jornada laboral (HH_INI), la hora de fin (HH_FIN), y su duración (HH_JOR). También aprovechar la función de días laborales para que también tome en cuenta los festivos, par lo cual se le asigna como tercer parámetro los días laborales que apliquen.

La formula queda así:

En el siguiente link se encuentra disponible un archivo Microsoft Excel que contiene el desarrollo de ambas formulas.

Identificar o eliminar repetidos con Microsoft Access

Como complemento a la publicación anterior, donde se muestra como identificar registros repetidos en hojas de cálculo, se dearrollaron técnicas similares para bases de datos. Este es el turno de Microsoft Access.

Como ejemplo se utilizará una supuesta tabla "nombre_tabla", que contiene al menos los campos "campo1", "campo2" y "campo3", y se considera que un registro está duplicado cuando estos tres campos son iguales.

Esta operación no se pude realizar directamente con Microsoft Access (o se desconoce) pero si se logra realizando un par de pasos intermedios.

Primero se debe de crear un nuevo campo al que se le llamará "rownum" de tipo Autonumérico (se selecciona la tabla "nombre_tabla", se presiona Vista de Diseño, y se agrega el campo al final).

Al confirmar la operación, Microsoft Access creará la nueva columna "rownum" y la completará con números del 1 hasta la cantidad de filas que tenga la tabla.

Luego se debe de crear una consulta de actualización sobre esta tabla (dentro de las consultas, se selecciona nueva consulta en vista de diseño, se selecciona la tabla "nombre_tabla" o cualquier otra, se presiona el botón SQL, y se cambia lo que está escrito por la siguiente sentencia:


Esta sentencia borrará los registros que para iguales campos 1 al 3 tengan el valor de rownum mayor al mínimo.

Al ejecutarla se deberá confirmar la operación (indicará la cantidad de registros borrados).

En caso de que no se desee borrar los registros duplicados, sino solamente marcarlos se deberá de crear otro campo adicional por ejemplo de tipo texto con largo 2, en este ejemplo con nombre "EsRepetido".

Al crear el campo, si no se especifica un valor predeterminado para los registros ya existentes, Microsoft Access le asigna el valor nulo. Si se desea se le puede asignar como valor predeterminado el texto No. O de lo contrario ejecutar la siguiente consulta de actualización:


Aquí se está inicializando el atributo que indica el registro está o no repetido.

Posteriormente se ejecuta la siguiente consulta de actualización para marcar los repetidos.


Esta actualización utiliza el mismo criterio que el borrado.

En el caso que se requiera marcar los no repetidos se logra con la siguiente consulta de actualización:


Para este caso se marcan como no repetidos aquellos registros que su "rownum" sea igual al mínimo que existe entre todos los registros que tengan mismo campos 1 al 3.

El nuevo campo "rownum" se puede eliminar o dejarlo para posteriores depuraciones.

Validación con Listas desplegables: información dinámica, en otras hojas y más con Excel o similar

Cuando se requiere que ingresar información en una planilla de cálculo los datos sean seleccionados de una lista desplegable se debe de realizar una validación del tipo lista, esta actúa tanto como ayuda para el ingreso datos como para validar la calidad de los mismos.

En la web hay muchas preguntas sobre como realizar estas listas con valores dinámicos dependiendo del contenido de otra celda (ej. si se están ingresando países y ciudades, al mostrar la lista de ciudades muestre solo las del país antes ingresado), como hacer cuando la información a validar no se encuentra en la propia hoja o libro, evitar valores repetidos, y otras más; muchas de estas no están respondidas.

Este artículo pretende ser una guía de como realizar estas listas intentando abarcar todas las posibilidades.

Está basado en Microsoft Excel, también se mencionará como hacerlo en OpenOffice.org Calc donde la operativa es más permisible. No se incluye información sobre Google Docs ya que este tipo de validación no está disponible.

El contenido es el siguiente:

1. Listas de validación básicas en la misma hoja
2. Listas de validación con origen de datos en otra hoja del mismo libro.
3. Listas de validación con origen de datos en otra hoja de otro libro.

Como se está publicando a medida en que se desarrolla cada capítulo, proximamente estarán disponible la continuación que incluye:

4. Quitar valores repetidos.
5. No incluir espacios en blanco.
6. Listas de validación con información variable según valor de otra celda.
7. Listas de validación con información variable según valor de otra columna.

Si hay algún otra parte que consideras que se deba de incluir, se agradecerá hacerlo llegar mediante comentarios o correo electrónico.

Listas de validación con origen de datos almacenados en otro libro

En el capítulo anterior de la serie validación con listas desplegables se mostró como realizar una validación utilizando listas con origen de datos en otra hoja del mismo libro que los datos a validar, ahora se mostrará una variante para cuando la lista a validar se encuentra en otro libro.

Se trabajará con un ejemplo de una lista de personas donde en la primer columna se tendrá el nombre, en la segunda el país de residencia y por último la ciudad donde vive.

La información sobre los países y las ciudades se validará mediante una lista de manera que el dato a ingresar sea mostrado mediante listas predefinidas.

La lista de los países válidos se encuentra en un segundo libro que tiene una hoja llamada "Lista" donde en el rango I3:I6 de la hoja, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo con la lista de validación se encuentra aquí, y el que tiene la lista de los datos aquí. Se deberán de bajar ambos ficheros y dejarlos en una misma carpeta para que funcione correctamente.

Para comenzar se deben tener abiertos ambos libros, y estar en el mismo grupo de libros abiertos.

Desde el archivo donde se encuentran las listas a validar se deberán de crear los nombres de rangos de celdas que apunten al otro libro, esta vez en la referencia se deberá de anteponer el nombre del archivo donde se encuentran los datos a validar.

Al igual que en el capítulo anterior acceder a definir los nombres de las referencias (desde Insertar / Nombre / Definir), para facilitar la operación, presionar el botón que se encuentra a la derecha del "Se refiere a" donde aparecerá una pequeña ventanita, pasarse al siguiente libro con Alt+Tab, seleccionar el rango de países, y volver a presionar el botón de la derecha.



Paso siguiente se deben de definir las listas de validación para cada columna, primero seleccionar la columna correspondiente a los países, elegir la validación tipo lista (en Datos / Validación).

Si se asigna en "Origen" el nombre del rango correspondiente a la lista de países Excel no lo permitirá, emitiendo el siguiente error: "No se pueden usar referencias a otros libros u hojas de cálculo para criterios de Validación de datos".


Por lo que se deberá de utilizar la función INDIRECTO para que pase la validación "engañándo" a Excel.


Esta solución es muy similar a la tercer variante del ejemplo del capítulo anterior. La segunda también es válida, donde a la función indirecto se le pasa la dirección como tal, pero se considera mejor la opción con nombre de referencia ya que si realizan modificaciones sobre el rango de origen ésta se actualiza sola.

Como punto en contra de esta solución es que al utilizar la función INDIRECTO el libro con el origen de los datos debe de estar abierto, de lo contrario al actualizar la información retorna un error.

Realizar los mismos pasos para las ciudades, tanto el rango de datos como la validación.

Listas de validación con origen de datos almacenados en otra hoja

En el capítulo anterior de la serie validación con listas desplegables se mostró como realizar una validación utilizando listas básicas donde los datos que la componen se encuentran en la misma hoja, ahora se mostrará como hacer para crear las listas cuando los datos se encuentran en otra hoja del mismo libro.

Al momento de definir el origen de datos que componen las listas de validación, Microsoft Excel verifica que la referencia a estos datos estén en la misma hoja de cálculo que la celda a validar, por lo que en principio no se puede almacenar los datos de las listas en otra hoja (por ejemplo Lista).

Al verificar la referencia, Microsoft Excel revisa que en la misma no incluya el nombre de hoja de cálculo o libro, si lo detecta lo impide y presenta el siguiente error:


Para evitarlo, la idea es lograr escribir una referencia a un rango que apunte a otra hoja sin poner el nombre de la hoja.

Aquí se mostrarán 3 formas de como validar datos mediante una lista desplegable donde su origen se encuentre en otra hoja de cálculo del mismo libro.

Se trabajará con un ejemplo de una lista de personas donde en la primer columna se tendrá el nombre, en la segunda el país de residencia y por último la ciudad donde vive (hoja Datos).

La información sobre los países y las ciudades se validará mediante una lista de manera que el dato a ingresar sea mostrado mediante listas predefinidas.

La lista de los países válidos se encuentra en la hoja Lista en el rango I3:I6 de la hoja, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo completo está disponible aquí.

La primer forma de "engañarlo" es crear un nombre de rango y hacer referencia a éste en la casilla de validación.

Para esto se debe de seleccionar el rango con los datos a desplegar, y luego la opción "Nombre" del menún "Insertar" y allí seleccionar "Definir ...".


Allí se debe definir un nombre, en este ejemplo LISTA_PAISES, y donde contiene "Se refiere a" debe de estar la referencia del rango seleccionado (deberá aparecer con referencias absolutas, e incluir el nombre de la hoja).

Presionar Agregar para confirmar el nombre.


Paso siguiente se selecciona las celdas donde se ingresarán los países (de la misma forma que en el caso anterior), pero en la opción "Origen" se debe de ingresar el nombre del rango definido como

=LISTA_PAISES


Como segunda forma se puede utilizar la función INDIRECTO. Esta función convierte un texto que contiene una referencia a una celda o rango en una referencia como tal.
Aquí se debe de escribir la la dirección del rango completo en modo de texto.

En Origen se debe de ingresar:

=INDIRECTO("Lista!$A$2:$A$5")

Lo malo de esta forma es que si hay cambios en la referencia tales como cambio del nombre de la hoja, se inserta una columna antes de la A, o se insertan filas la dirección no será recalculada automáticamente y quedará apuntando a una lista errónea.

Y la tercer forma es la combinación de las anteriores: utilizando la función INDIRECTO a la que se le pasa como parámetro el nombre del rango en forma de texto.

=INDIRECTO("LISTA_PAISES")

Esta versión no presenta el problema antes mencionado ya que se trata de un nombre de rango que es modificado automáticamente por Microsoft Excel cuando se realiza un cambio.

No cabe deudas que la primer opción es la mejor y más sencilla, las otras variantes se muestran debido a que en los siguientes capítulos se van a utilizar.

Hacer el mismo procedimiento para la lista de ciudades, esta vez definiendo al rango C2:C32 como LISTA_CUIDADES.

Para la primer opción en Origen se tiene que definir:

=LISTA_CUIDADES

Para la segunda opción en Origen se tiene que definir:

=INDIRECTO("Lista!$C$2:$C$32")

Y para la tercer opción en Origen se tiene que definir:

=INDIRECTO("LISTA_CUIDADES")

Listas de validación básicas en la misma hoja con Excel

Este artículo es el primer capitulo de la serie Listas desplegables

Para comenzar se mostrará un ejemplo básico de como realizar la validación tipo lista tal como lo contempla Microsoft Excel, el objetivo es conocer la operativa y las limitantes de la herramienta.

trabajará con un ejemplo de una lista de personas donde en la primer columna se tendrá el nombre, en la segunda el país de residencia y por último la ciudad donde vive. La información sobre los países y las ciudades se validará mediante una lista de manera que el dato a ingresar sea mostrado mediante listas predefinidas.

La lista de los países válidos se encuentra en el rango I3:I6, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo completo está disponible aquí.

Los pasos para realizar una lista de selección son los siguientes:

Seleccionar el rango de celdas, o columna completa, donde se ingresará y validará la información de los países, para el ejemplo se seleccionará el rango C3:C30.


Acceder a la opción "Validación ..." del menú "Datos".
Allí se debe de definir el Criterio de validación, para esto se deberá seleccionar "Lista" en la opción Permitir. En la opción Origen se debe de introducir el rango de las datos que se desean mostrar en la lista, en este caso se trata del rango $I$3:$I$6 (el rango debe de estar definido mediante referencias absolutas). Se deberá dejar seleccionada la opción "Celda con lista desplegable".

Si se desea, en la pestaña "Mensaje entrante" se puede ingresar un mensaje que muestra un comentario al momento de seleccionar la celda para ingresar la información. Si no se ingresa nada no muestra ningún comentario.

También como opcional, en la pestaña "Mensaje de error" se puede personalizar un mensaje de error para el caso de que se ingrese un dato no valido, en este caso un país que no se encuentra en la lista (o está mal escrito). De no definirse, al ingresar un dato erróneo aparecerá un mensaje genérico.


Con estos pasos la lista ya está definida, ahora al hacer clic sobre una celda para ingresar los países aparecerá una lista con los países válidos.


Los comentarios ingresados se verán de la siguiente manera:


Aunque se tenga la lista para el ingreso de datos, es permitido ingresar el valor digitándolo directamente, aquí es donde entra a tomar sentido la validación, en caso de que el valor ingresado no esté dentro de la lista, y se haya definido un mensaje de error el se vería de la siguiente manera:


Hay que tener en cuenta de que si en la lista seleccionada existen datos duplicados, al desplgarse los datos también estarán repetidos (más adelante se mostrará como evitarlo). Si la lista original cuenta con celdas nulas, mostrará los mismos renglones vacíos en la lista.

Ahora si se desea mostrar una segunda lista con las ciudades, se deberá realizar el mismo proceso utilizando la lista de ciudades. El principal con la restricción que no se puede desplegar solamente las ciudades que pertenezcan al país seleccionado en la misma fila, se mostrarán todas.

En el ejemplo se deberá por error se podrá ingresar como país Colombia y ciudad Buenos Aires. También hay problemas con ciudades de diferentes países que tienen el mismo nombre como ser Córdoba que está en España, México y Argentina. En la lista aparecerá tres veces.

Si se intenta introducir un origen de datos de datos válidos que se encuentre en una hoja de cálculo diferente a donde se encuentra la o las celdas a validar, Microsoft Excel no lo permite.

Desglosar dinero en billetes y monedas de diferentes denominaciones con Excel o similar

Cuando se tiene, por ejemplo, pagar nómina de contado es necesario conocer cuantos billetes y monedas de diferentes denominaciones se requieren para solicitarlos en el banco de manera de poder pagar con el monto exacto a cada uno.

El proceso es el siguiente:

- En una hoja de cálculo se deberá de poner en una columna el importe que se quiere desglosar y tantas columnas como diferentes denominaciones de la moneda corriente existan, desde el billete de mayor valor hasta la moneda de menor valor. Como título de estas columnas se pondrá el propio valor y debe de ser en formato numérico ya que se va a utilizar para realizar cálculos.

- El propio cálculo se separará en dos condiciones: uno para el billete de mayor denominación y otro para el resto.

- Para el de mayor denominación simplemente el importe se divide por el valor del billete, y se trunca el resultado de manera de tener la cantidad de billetes necesarios. A nivel de fórmulas, queda de la siguiente manera:


Esta fórmula se encuentra en C2, en B2 el importe y en C1 el valor del billete. Se debe de copiar para todos los importes a distribuir.

Para el resto de denominaciones la formula cambia ya que antes de hacer la operación arriba mostrada se debe de restar al importe original lo ya distribuido.
La formula para el segundo billete de mayor dominación es la siguiente:


Donde:

B2 es el importe a procesar, nótese que tiene referencia mixta, se fija la columna importe y se deja libre la fila.
En D1 se encuentra el valor del billete, también tiene referencia mixta pero esta vez queda libre la columna para poder cambiarse de nominación al copiar la celda y siempre fija la fila 1.
El rango $C$1:C$1 contiene los valores de las nominaciones anteriores al que se está procesando, para este caso solo la columna C. El inicio del rango siempre será fijo, y en para el final se irá variando la columna de manera de que al copiar se extienda el rango a todas las nominaciones anteriores. Para la siguiente será $C$1:D$1.
El rango $C2:C2 contiene las cantidades ya distribuidas.
Con SUMAPRODUCTO se irá acumulando el producto de ambos rango celda a celda: C1*C2 + D1*D2, de esta manera se obtiene lo ya distribuido.
Hay una función REDONDEAR que parece estar de más, pero es para evitar problemas de precisión de Excel (cuando el resultado esperado es 30 puede que realmente retorne 29,9999999), se redondea a 5 decimales de manera de no interferir con la operativa.

Esta función se debe de copiar para el resto de las nominaciones y todos los importes a distribuir.

Al final se suman todas las cantidades de cada denominación de manera de saber el total requerido.

Desglosar dinero en billetes y monedas de diferentes denominaciones con Microsoft Excel

Las formulas son las utilizadas en la muestra del proceso.

En C2 introducir esta fórmula para el cálculo de la cantidad de billetes con denominación mas alta:


En D2 introducir esta fórmula para el cálculo de la cantidad de billetes con la segunda denominación más alta:


Copiarla para el resto de billetes y monedas.

Aquí se encuentra disponible un archivo de ejemplo utilizando Euros que contiene dos hojas: en la primera un detalle donde se desglosan los importes y en la segunda un resumen con la información total.

Desglosar dinero en billetes y monedas de diferentes denominaciones con OpenOffice.org Calc

Las formulas a utilizar son las siguientes:

En C2 introducir esta fórmula para el cálculo de la cantidad de billetes con denominación mas alta:


En D2 introducir esta fórmula para el cálculo de la cantidad de billetes con la segunda denominación más alta


Copiarla para el resto de billetes y monedas.

Al igual que en Excel, aquí se encuentra disponible un archivo de ejemplo en OpenOffice.org Calc utilizando euros que contiene dos hojas: en la primera un detalle donde se desglosan los importes y en la segunda un resumen con la información total.