Brackwell Facilities vigiló un centro logístico durante todo noviembre: 38 turnos, 19 de ellos nocturnos. Nómina pagó 108,50 € por el mes. El cuadrante valía 3.468,50 €, y ni una sola hora del parte estaba mal escrita.
La celda del total guardaba el valor correcto. Mostraba 7:45, porque tenía formato h:mm, y un formato de reloj no tiene dónde poner las 240 horas anteriores. La administrativa tecleó lo que decía la pantalla.
| Medida | Pagado | Trabajado |
|---|---|---|
| Turnos | 38 | 38 |
| Turnos nocturnos | 19 | 19 |
| La celda del total | 7:45 | 247:45 |
| Tecleado en nómina | 7,75 | 247,75 |
| Pagado a 14,00 € la hora | 108,50 € | 3.468,50 € |
Una hora en Excel es una fracción de día, y el día es donde h:mm deja de contar. Las almohadillas de los turnos de noche, el sueldo que salió por un veinticuatroavo y el total que volvió a empezar son el mismo hecho, encontrado tres veces.
01El Total Que Volvía a Empezar a Medianoche
Ocho Turnos, Cuatro de Ellos con Almohadillas
Ocho turnos del cuadrante de noviembre de Brackwell Facilities, en A2:H9. La columna B es la entrada y la columna C la salida, las dos como horas del día. La columna D es la resta que escribiría cualquiera, =C2-B2, correcta en los cuatro turnos de día y con ###### en los cuatro que cruzan medianoche. La columna E es esa misma resta envuelta en =RESIDUO(C2-B2;1). La columna F es ese valor multiplicado por 24, para poder sumar, comparar y pagar las horas, y la columna G es la columna F a 14,00 € la hora. Estos ocho turnos suman 74,75 horas. El mes completo tiene 38 turnos, 19 de ellos nocturnos, y suma 247,75 horas: 247:45 en una celda con formato [h]:mm, y 7:45 en una celda con formato h:mm.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
La columna D es la resta que escribiría cualquiera: =C2-B2, el fin del turno menos el inicio. Es correcta en todos los turnos de día e imprime ###### en todos los de noche.
La columna E es esa misma resta envuelta en RESIDUO, y la columna F es ese valor multiplicado por 24 para poder sumar, comparar y pagar. Estos ocho turnos suman 74,75 horas; el mes entero suma 247,75.
El parte que llegó a nómina no tenía columna E. Las filas de noche se habían reescrito a mano, el total vivía en una celda con formato h:mm, y el número de la pantalla era la décima parte del número de la celda.
Escenario: Pon =SUMA(E2:E9) debajo de tu columna de horas pagadas y da al resultado el formato [h]:mm. Si la cifra salta 24 horas o más en cuanto añades los corchetes, la celda se estaba dando la vuelta y el mes de alguien salió corto.
02Una Hora es una Fracción de Día
Excel guarda una fecha como un número entero de días y una hora como la fracción que sobra. El mediodía es 0,5. Una hora es 1/24, un minuto es 1/1440 y un segundo es 1/86400.
Por eso 8:30 y 0,354167 son la misma celda con dos formatos. Multiplica una hora por 24 y tienes horas como un número que se puede pagar: =0,354167*24 es 8,5.
| En pantalla | Valor guardado | × 24 |
|---|---|---|
| 0:15 | 0,010417 | 0,25 |
| 8:30 | 0,354167 | 8,50 |
| 12:00 | 0,500000 | 12,00 |
| 24:00 | 1,000000 | 24,00 |
| 247:45 | 10,322917 | 247,75 |
NSHORA(8;30;0) escribe esa fracción sin que nadie teclee decimales, y es la forma segura de meter una duración en una fórmula. Eso sí, NSHORA se da la vuelta a las 24 horas: =NSHORA(26;0;0) devuelve 2:00, no 26 horas.
Para cualquier cosa más larga que un día, escribe la división. Veintiséis horas son =26/24, que es más corto, se sigue entendiendo seis meses después y nunca se da la vuelta.
Escenario: Escribe 8:30 en una celda y =A1*24 al lado, y da a la segunda formato de número normal. Espera 8,5. Luego pon =NSHORA(26;0;0) debajo y mira cómo vuelve como 2:00.
03Por Qué los Turnos de Noche Imprimían
Un turno de 22:00 a 06:30 es 0,916667 menos 0,270833, es decir −0,645833. Excel no puede mostrar una hora negativa en el sistema de fechas de 1900, así que llena la celda de almohadillas.
Ensanchar la columna no sirve de nada, porque la columna no es estrecha. El valor es negativo, y ###### es la única forma que tiene un formato de hora de decirlo.
RESIDUO lo arregla con una vuelta:
=RESIDUO(C2-B2;1)
RESIDUO devuelve el resto de una división, y un numerador negativo vuelve en positivo: =RESIDUO(-0,645833;1) es 0,354167, o sea 8:30. En un turno de día la resta ya es positiva y RESIDUO la deja igual.
Una sola fórmula cubre los dos casos, así que el mes entero se suma en una celda: =SUMAPRODUCTO(RESIDUO(C2:C39-B2:B39;1))*24 devuelve 247,75. Son las horas, como número, sin ningún formato entre la hoja y la respuesta.
Alcance.
RESIDUOda por hecho que ningún turno dura más de 24 horas. Una guardia de 26 horas volvería como 2 horas, en silencio, así que acompaña el arreglo con una comprobación:=CONTAR.SI(E2:E39;">=1")debería dar cero.El sistema de fechas de 1904 de Excel sí admite horas negativas, y un libro cambiado a ese sistema muestra −15:30 en vez de almohadillas. Cambiarlo desplaza cuatro años y un día todas las fechas del archivo, así que es un ajuste que conviene reconocer, no activar.
Escenario: Escribe 22:00 y 06:30 en dos celdas, resta una de la otra y mira aparecer las almohadillas. Envuelve esa misma resta en =RESIDUO(C2-B2;1) y confirma 8:30 antes de fiarte de ningún total construido sobre esa columna.
04Los Corchetes Que Dejan Seguir Contando
h:mm muestra una hora del día, así que informa de un total como lo haría un reloj: 247 horas y 45 minutos se convierten en 7:45, porque 240 horas son diez días enteros y un reloj tira los días.
[h]:mm los conserva. Los corchetes le dicen a Excel que no dé la vuelta a la unidad, y la misma celda pasa a leerse 247:45. El valor guardado no cambia en nada; solo cambia el número que lee una persona.
Ponlo en la celda del total: Ctrl+1, Número, Personalizada, y escribe [h]:mm en el cuadro Tipo. [mm] hace lo mismo con los minutos — 14.865 aquí — y [ss] con los segundos.
| Formato | 247:45 se ve como | Para qué sirve |
|---|---|---|
h:mm | 7:45 | Una hora del día |
[h]:mm | 247:45 | Una duración |
[mm] | 14865 | Minutos trabajados |
| General | 10,32 | Para nada en nómina |
Aquí la fórmula nunca estuvo mal, y eso es lo que salió caro. Un #¡VALOR! se investiga esa misma mañana; un 7:45 verosímil al pie de un mes de vigilancia nocturna se teclea en nómina.
Escenario: Selecciona el total del mes, pulsa Ctrl+1, elige Personalizada y escribe [h]:mm. Lee la cifra antes y después. La diferencia entre las dos es un múltiplo de 24 horas, y es el sueldo que alguien no cobró.
05De Horas a Dinero: el ×24 Que Nadie Escribe
Multiplicar una celda de hora por una tarifa paga un veinticuatroavo del sueldo. =E2*14 en un turno de 8:30 devuelve 4,958, que se redondea a 4,96 €, porque E2 guarda 0,354167 de día y no 8,5 horas.
El 24 va antes que la tarifa:
=REDONDEAR(RESIDUO(C2-B2;1)*24*14;2)
REDONDEAR a dos decimales también pinta ahí. El sueldo es dinero, y un 119,0000000001 € sin redondear se porta bien en pantalla y mal en un total que alguien cuadra al céntimo.
| Turno | Horas pagadas | =E2*14 | =E2*24*14 |
|---|---|---|---|
| 8:00 | 0,333333 | 4,67 € | 112,00 € |
| 8:30 | 0,354167 | 4,96 € | 119,00 € |
| 12:15 | 0,510417 | 7,15 € | 171,50 € |
| 247:45 | 10,322917 | 144,52 € | 3.468,50 € |
Ten una sola columna de horas decimales y paga desde ahí. En cuanto la columna F guarda 8,50 en vez de 8:30, cualquier tarifa, plus o cuadre que venga después es aritmética normal.
Escenario: Añade =F2*14 junto a tus horas decimales y =E2*14 junto a los valores de hora. Comprueba las dos con una calculadora en un turno; la segunda siempre es la primera dividida entre 24.
06Redondear los Fichajes al Cuarto de Hora
Casi todos los partes redondean al cuarto de hora. Redondea los fichajes, no el total: redondear una sola vez al final esconde treinta y ocho diferencias pequeñas dentro de un número que nadie puede comprobar.
REDOND.MULT ajusta una hora al múltiplo más cercano:
=REDOND.MULT(B2;NSHORA(0;15;0))
Un fichaje de 22:07 pasa a 22:00 y uno de 22:08 pasa a 22:15. Aplicar la misma fórmula a la salida mantiene la pareja coherente, que importa más que hacia dónde se movió un fichaje suelto.
El redondeo en un solo sentido es MULTIPLO.SUPERIOR y MULTIPLO.INFERIOR. =MULTIPLO.SUPERIOR(B2;NSHORA(0;15;0)) empuja la entrada al cuarto siguiente y =MULTIPLO.INFERIOR(C2;NSHORA(0;15;0)) echa la salida hacia atrás, que es el lado de la empresa en cada frontera.
Mira el convenio antes de elegir. El redondeo es una condición laboral y no una preferencia de formato, y tanto en el Reino Unido como en España se espera que salga neutro con el tiempo en lugar de siempre a favor de la empresa.
La tolerancia de entrada es otra cosa.
=SI(ABS(B2-$J$1)<=NSHORA(0;5;0);$J$1;B2)trata como puntual cualquier fichaje a menos de cinco minutos de la hora del cuadrante, y deja en paz al que llegó tarde de verdad.
Escenario: Pon =REDOND.MULT(B2;NSHORA(0;15;0)) junto a un fichaje de 22:07 y espera 22:00. Cambia el fichaje a 22:08 y espera 22:15. Luego compara el total del mes antes y después de redondear; la diferencia debería ser menor que una hora.
07Los Descansos se Restan Antes de las Horas Extra
Un descanso no pagado de 30 minutos es una resta, y NSHORA la escribe: =RESIDUO(C2-B2;1)-NSHORA(0;30;0). En un turno de 12:15 quedan 11:45 pagadas.
El descanso suele depender de la duración del turno, así que la resta tiene que ser condicional en vez de ir tecleada en cada fila:
=LET(trabajado; RESIDUO(C2-B2;1);
desc; SI(trabajado>NSHORA(6;0;0); NSHORA(0;30;0); 0);
MAX(0; trabajado - desc))
LET nombra la resta una vez y la usa tres, así que una corrección se hace en un solo sitio. MAX(0;…) evita que un turno corto se vuelva negativo y devuelva las almohadillas que quitó la sección 3.
Resta primero el descanso y separa después las horas extra. Al revés se paga un plus por media hora que el vigilante pasó comiendo, y en 19 turnos de noche eso son nueve horas y media a hora y media.
Escenario: Construye la versión con LET en una columna libre y pruébala en tres turnos: uno de cinco horas, uno de seis y el miércoles de 12:15. Espera cero descuento en los dos primeros y 11:45 en el tercero.
08Separar las Horas Base de las Extra
Con las horas en decimal, la separación son dos funciones y ningún anidamiento. Las base son =MIN(F2;8) y las extra =MAX(0;F2-8).
Siempre vuelven a sumar el turno, que es justo la propiedad que pierde una cadena de SI anidados la primera vez que alguien cambia un umbral en una fila y no en la siguiente.
| Turno | Horas pagadas | =MIN(F2;8) | =MAX(0;F2-8) | Pago a 14 € y 21 € |
|---|---|---|---|---|
| Lun día | 8,00 | 8,00 | 0,00 | 112,00 € |
| Lun noche | 8,50 | 8,00 | 0,50 | 122,50 € |
| Mié día | 12,25 | 8,00 | 4,25 | 201,25 € |
| Dom día | 12,00 | 8,00 | 4,00 | 196,00 € |
Las extra semanales tienen la misma forma contra el total de la semana: =MAX(0;SUMA(F2:F8)-40). Elige diarias o semanales, nunca las dos, o las horas que pasan de ocho el miércoles cobran plus dos veces.
Escenario: Añade las dos columnas a tu hoja y pon =SUMA(G2:G39)+SUMA(H2:H39)-SUMA(F2:F39) debajo. Tiene que dar cero. Cualquier otra cosa significa un umbral tecleado en una fila y no en la de abajo.
09Totales por Vigilante, no por Parte
El total del mes es para la factura. Las preguntas que vuelven son sobre personas: quién hizo las 19 noches, quién pasa de 48 horas en una semana, a quién le pagaron un turno que había cambiado.
SUMAR.SI.CONJUNTO suma horas igual de bien que dinero, y el ×24 va por fuera:
=SUMAR.SI.CONJUNTO($E$2:$E$39;$A$2:$A$39;$J2)*24
=CONTAR.SI($A$2:$A$39;$J2) cuenta los turnos que hay detrás de esa cifra y =PROMEDIO($F$2:$F$39) da la duración media. Un vigilante con 9 turnos de 13 horas de media es un problema de cuadrante, y el total del mes por sí solo no lo enseña.
Cada uno de esos resultados sigue siendo una hora hasta que algo lo multiplica por 24, así que una tabla resumen necesita el mismo [h]:mm, o el mismo ×24, que el total del que salió.
Escenario: Monta una tabla pequeña de nombres en la columna J y pon el SUMAR.SI.CONJUNTO al lado de cada uno. Comprueba que la columna suma 247,75; si se queda corta, hay un nombre escrito de dos maneras en la columna A.
10Cuando un Fichaje es Texto y SUMA lo Ignora
Un fichaje pegado desde un control de accesos llega a menudo como texto: 22:00 con un espacio final, 8.30 con un punto, o con un apóstrofo que alguien escribió para que Excel no le cambiara el formato.
El texto se alinea a la izquierda y SUMA se lo salta sin decir nada. La comprobación es el recuento:
=CONTAR(B2:B39)
CONTAR solo cuenta números, así que cualquier cifra por debajo de 38 es un fichaje que no es una hora. =CONTARA(B2:B39) cuenta todo, y la diferencia entre los dos es exactamente cuántas celdas hay que arreglar.
=HORANUMERO(ESPACIOS(B2)) convierte una de ellas: ESPACIOS quita el espacio final y HORANUMERO lee lo que queda como hora. Un punto en lugar de dos puntos necesita antes =VALOR(SUSTITUIR(B2;".";":")).
Escenario: Pon =CONTAR(B2:B39) y =CONTARA(B2:B39) debajo de tu columna de fichajes. Si no coinciden, selecciona la columna, abre Texto en columnas y termina en la primera pantalla; vuelve a interpretar cada celda y suele cerrar la diferencia.
11Enviar las Horas al Sistema de Nómina
Casi todos los sistemas de nómina quieren horas decimales y no un reloj: 8,5, no 8:30. =REDONDEAR(F2;2) es toda la exportación, y la columna tiene que tener formato de número o el archivo escribirá 0,35.
Cuando el sistema quiere HH:MM como texto, =TEXTO(E2;"[h]:mm") lo escribe con los corchetes puestos, así que un total de 247:45 llega como texto en vez de aparecer como 7:45.
HORA se da la vuelta donde se la da el formato. =HORA(E40) sobre el total del mes devuelve 7, no 247, porque HORA informa de una hora del día. Parte una duración larga con aritmética:
=ENTERO(F40)&"h "&REDONDEAR(RESIDUO(F40;1)*60;0)&"m"
Escenario: Escribe =TEXTO(E40;"[h]:mm") y =HORA(E40) junto al total del mes. El primero pone 247:45 y el segundo pone 7, que es el mismo fallo con el que empezó este artículo disfrazado de otra cosa.
12Ocho Cosas Que Muerden
- Un total con formato
h:mm. Correcto los diez primeros días de un mes de noches y equivocado en exactamente 24 horas cada día posterior. =C2-B2en un turno de noche. Negativo, con almohadillas, y mucho más a menudo reescrito a mano que arreglado.- Pagar una celda de hora.
=E2*14es un veinticuatroavo del sueldo, y parece un número pequeño verosímil en vez de un error. NSHORA(26;0;0). Se da la vuelta a 2:00. Una duración de más de un día se escribe26/24.HORAsobre una duración. Informa del reloj, así que un total de 247:45 vuelve como 7.- Redondear el total en vez de los fichajes. Un número limpio y treinta y ocho diferencias que nadie sabe explicar.
- Descansos restados después de separar las extra. Paga un plus por media hora no pagada, en cada turno largo, todo el año.
- Un fichaje que es texto.
SUMAse lo salta en silencio, yCONTARes lo único de la hoja que se da cuenta.
13Mini Ejercicios
- Resta
22:00de06:30en una celda libre y espera######. Envuélvelo en=RESIDUO(C2-B2;1)y espera 8:30. - Suma la columna E con
=SUMA(E2:E9)y dale formatoh:mmy después[h]:mm. Espera 2:45 y luego 74:45. - Paga el mes de las dos maneras, con
=E40*14y con=F40*14. Espera 144,52 € y 3.468,50 €. - Redondea un fichaje de 22:07 con
=REDOND.MULT(B2;NSHORA(0;15;0))y espera 22:00; cámbialo a 22:08 y espera 22:15. - Separa el turno del miércoles con
=MIN(F5;8)y=MAX(0;F5-8). Espera 8,00 y 4,25, y 12,25 al volver a sumarlas.
Lo Que Hay Que Llevarse
En este parte no había nada mal escrito. Treinta y ocho turnos anotados al minuto, restas que habría escrito cualquiera, y una celda al pie que guardaba exactamente el número correcto. Mostraba la décima parte.
El tiempo en Excel es una unidad con techo. Un día es 1, h:mm se para a las 24 horas, NSHORA se para a las 24 horas y HORA se para a las 24 horas. Todos los fallos de este artículo son uno de esos techos a toda velocidad.
Así que quédate con dos costumbres y el resto viene solo: envuelve cada turno en RESIDUO y da a cada total el formato [h]:mm. Luego multiplica por 24 una vez, paga desde la columna decimal, y el mes valdrá lo que valía.