Ingrese la hora sin dos puntos (hhmmss) y calcule las diferencias horarias en Excel

Question 1

Una opción sería utilizar una combinación de formato de celda con Excel.TIEMPOfunciones.

Formatee sus celdas de entrada de tiempo (A2 y B2 en mi ejemplo) como Text. El formato esperado siempre será hhmmss, por lo tanto, ingrese el cero inicial para las horas con horas de un solo dígito. Entonces puedes calcular con esta fórmula:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Esto toma los 2 caracteres del extremo izquierdo como 'Horas', los 2 caracteres del medio como 'Minutos' y los 2 caracteres del extremo derecho como 'Segundos' y los convierte a lo que Excel reconoce como una hora. Luego resta uno del otro y muestra el resultado, con el formato hhmmss:

EDITAR: dado que el requisito no es el especificado en la pregunta, modifiqué la fórmula para tener en cuenta los ceros iniciales al completarla:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

Horriblemente menos legible, pero ahora rellena el valor con ceros y usa el 6 más a la derecha, por lo que debería funcionar independientemente de cuántos ceros uses.

Creo que en realidad querrás darle un formato especial al resultado como hh:mm:ssen este caso.

Answer

Una opción sería utilizar una combinación de formato de celda con Excel.TIEMPOfunciones.

Formatee sus celdas de entrada de tiempo (A2 y B2 en mi ejemplo) como Text. El formato esperado siempre será hhmmss, por lo tanto, ingrese el cero inicial para las horas con horas de un solo dígito. Entonces puedes calcular con esta fórmula:

=TIME(LEFT(B2,2), MID(B2,3,2), RIGHT(B2,2))-TIME(LEFT(A2,2), MID(A2,3,2), RIGHT(A2,2))

Esto toma los 2 caracteres del extremo izquierdo como 'Horas', los 2 caracteres del medio como 'Minutos' y los 2 caracteres del extremo derecho como 'Segundos' y los convierte a lo que Excel reconoce como una hora. Luego resta uno del otro y muestra el resultado, con el formato hhmmss:

EDITAR: dado que el requisito no es el especificado en la pregunta, modifiqué la fórmula para tener en cuenta los ceros iniciales al completarla:

=TIME(LEFT(RIGHT("000000"&B2,6),2), MID(RIGHT("000000"&B2,6),3,2), RIGHT(RIGHT("000000"&B2,6),2))-TIME(LEFT(RIGHT("000000"&A2,6),2), MID(RIGHT("000000"&A2,6),3,2), RIGHT(RIGHT("000000"&A2,6),2))

Horriblemente menos legible, pero ahora rellena el valor con ceros y usa el 6 más a la derecha, por lo que debería funcionar independientemente de cuántos ceros uses.

Creo que en realidad querrás darle un formato especial al resultado como hh:mm:ssen este caso.

Question 2

Aquí hay otra forma de manejar la decodificación de sus tiempos de la forma en que desea ingresarlos:

Dejé las horas de inicio y finalización como números sin formato para que la acción sea más visible. En este ejemplo, su hora de inicio es 25 segundos después de la medianoche o el mediodía, que ingresaría como 000025. Como número, se almacenará como 25, aunque su formato hará que parezca 00:00:25.

Este enfoque elimina las horas y los minutos en función de su posición en el número, que es potencias de 100. Independientemente, los segundos siempre son los dos dígitos correctos. La fórmula en C2 es:

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

La función MOD es lo opuesto a la función INT. Te da el resto después de la división.

Answer

Aquí hay otra forma de manejar la decodificación de sus tiempos de la forma en que desea ingresarlos:

Dejé las horas de inicio y finalización como números sin formato para que la acción sea más visible. En este ejemplo, su hora de inicio es 25 segundos después de la medianoche o el mediodía, que ingresaría como 000025. Como número, se almacenará como 25, aunque su formato hará que parezca 00:00:25.

Este enfoque elimina las horas y los minutos en función de su posición en el número, que es potencias de 100. Independientemente, los segundos siempre son los dos dígitos correctos. La fórmula en C2 es:

= TIME(INT(B2/10000), INT(MOD(B2/10000,1)*100), RIGHT(B2,2))
 -TIME(INT(A2/10000), INT(MOD(A2/10000,1)*100), RIGHT(A2,2))

La función MOD es lo opuesto a la función INT. Te da el resto después de la división.

Question 3

Sé que esta es una publicación antigua, pero si (como lo hice yo) las personas están buscando una solución a través de innumerables foros para ahorrar tiempo al ingresar hojas de horas y no tener que ingresar manualmente "Dos puntos", pero tener los dos puntos visibles y luego ser capaz de realizar cálculos... todo sin usar VBA Script, celdas ocultas y fórmulas... etc... etc... Esta es la solución que se me ocurrió, y parece funcionar con los principales ceros, horario de 24 horas y hojas de horas que abarcan la medianoche (aunque sería necesario realizar ajustes si espera que una hoja de horas abarque varias medianoches; no vi que esto fuera necesario en mi caso).

Además, no necesito "Segundos", pero deberías poder expandirlo con bastante facilidad si sigues el concepto:

Formato personalizado de las celdas de hora de inicio y hora de finalización como:

0#":"##

Esto las convierte en "celdas de texto", pero rellena los dígitos para mostrar siempre cuatro dígitos (incluso con un cero al principio o un cero para los amigos estadounidenses).

Tenga en cuenta que esto permitirá que la entrada de CUALQUIER número de cuatro dígitos parezca un formato de hora. Entonces, en teoría, es posible ingresar a las 0768 y convertirse en la hora 07:68, que por supuesto no existe. Así que tampoco

a) cree una lista de validación de datos de la siguiente manera: - en una hoja de trabajo separada (pestaña), en una sola columna, cree una lista de todos los valores aceptables. En mi caso, esto fue desde las 0500, 0515, 0530, 0545.....0445 ya que solo estaba tratando con incrementos de 15 minutos. Si necesita profundizar en segundos (¿en serio?), esto sería bastante largo... es decir. 050001, 050002... etc. O puede escribir otra fórmula solo para crear esta lista y luego copiar los datos de texto (pegar solo valores) en una nueva columna para eliminar las fórmulas (yo lo haría). - Seleccione todas estas celdas de "valores aceptables) y en la pestaña "Forumulas" de la barra de herramientas seleccione "Definir nombre". Asígnele un nombre como "MonkeyTimeList". - Vuelva a su hoja de trabajo de horas, seleccione todas las celdas que cubren ambos inicios. y horas de finalización, y vaya a la pestaña "Datos" de la barra de herramientas, seleccione "Validación de datos>Validación de datos>Configuración", en la sección "Permitir", seleccione "Lista" en el menú desplegable, luego en la sección "Fuente" escriba en el nombre que definiste precedido por "=", entonces en mi caso "=MonkeyTimeList". Antes de hacer clic en "Aceptar", seleccione la pestaña "Alerta de error", marque la casilla "Mostrar alerta de error...", y en el En el menú desplegable "Estilo", seleccione "Detener". Siéntase libre de agregar un mensaje de error como "Monkey Man dice que es un idiota, intente ingresar los tiempos en el FORMATO CORRECTO". Luego haga clic en "Aceptar". de su lista "aceptable" se puede ingresar como una hora.

b) en teoría, podría usar el formato condicional solo para resaltar una celda donde se agregan datos incorrectos. Esto evitaría el uso de una hoja de trabajo separada para sus datos de validación de datos. No lo he hecho así, por eso digo teóricamente. Ese puede ser un tutorial diferente....

Entonces, ahora puede ingresar sus tiempos, sin tener que buscar los dos puntos y la tecla Mayús cada vez.

Si REALMENTE quieres segundos en tu formato de celda, entonces el formato de celda personalizado se vería así:

0#":"##":"##

ASÍ QUE... AHORA PARA CALCULAR LAS HORAS desde la hora de inicio hasta la hora de finalización... nuevamente, esto se basa solo en minutos... pero si puedes seguir la lógica, también se puede expandir a segundos.

En la tercera columna (suponiendo que la columna A es la hora de inicio, B es la hora de finalización y C es el total de horas), ingrese la fórmula:

=SI(VALOR(B2)>=VALOR(A2),(SUM(VALOR(MEDIO(TEXTO(B2,"0000"),1,2)),VALOR(MEDIO(TEXTO(B2,"0000"),3 ,2))/60))-(SUM(VALOR(MEDIO(TEXTO(A2,"0000"),1,2)),VALOR(MEDIO(TEXTO(A2,"0000"),3,2))/ 60)),(SUM(VALOR(MEDIO(TEXTO(B2,"0000"),1,2)),24,VALOR(MEDIO(TEXTO(B2,"0000"),3,2))/60)) -(SUM(VALOR(MEDIO(TEXTO(A2,"0000"),1,2)),VALOR(MEDIO(TEXTO(A2,"0000"),3,2))/60)))

Ahora a explicarlo...

La función "IF" distingue las horas de finalización en un reloj de 24 horas que son antes de medianoche y aquellas que son después de medianoche. SI es después de la medianoche, se agregan 24 horas a la hora de finalización para permitir que el cálculo de la hora de finalización menos la hora de inicio siga funcionando. SI es antes de medianoche, no es necesario sumar 24 horas. Entonces, para fines de cálculo, la hora 0100 en realidad se trata como 2500, 0200 es 2600 y así sucesivamente. NOTA. esto supone que un turno no dura más de 23 horas y 45 minutos. Si dura más, tal vez necesites factorizar las columnas de fecha en tu fórmula... repito, no lo necesitaba.

Las funciones MID funcionan en TEXTO (de ahí la función TEXTO) y devuelven los dígitos en ubicaciones específicas de una celda referenciada, en el formato especificado... Así que, "MID(TEXTO(B2,"0000"),1,2) "mira la celda B2, y siempre la mira en el formato de 4 dígitos/letras, y devuelve los dos dígitos comenzando en la posición "1". En mi caso esto representa horas enteras. Otro ejemplo: "MID(TEXT(A2,"000000"),5,2)" buscaría en la celda A2 y devolvería los dos dígitos comenzando en la posición "5", que podrían ser segundos completos.

Coloqué la función "VALOR" delante de todo esto para convertir el texto devuelto en un número, de modo que podamos trabajar con él en los cálculos.

Entonces, esto nos permite para cada celda tratar las horas, los minutos y los segundos por separado. Los cálculos con horas son sencillos ya que ya son números enteros. Sin embargo, los minutos son en realidad fracciones de horas, o fracciones de 60 minutos, por lo que una vez que extraigas los dígitos que representan "minutos", divídelos por "60" para obtener una "fracción" de una hora. Al igual que con los segundos, divida por 3600 (número de segundos en una hora) para obtener los segundos como una "fracción de una hora".

Ahora que los tiempos están representados como números con fracciones, puedes sumar o restar como quieras. En el caso de mi fórmula, dará el número total de horas entre la hora de inicio y la hora de finalización. Formateo la celda con la fórmula como Personalizado> 00.00, lo que luego me permite multiplicar por tarifas por hora, etc.

Si alguien todavía quiere segundos en su fórmula. y te quedas atascado, házmelo saber. Si estoy aburrido en el trabajo, tal vez pueda ayudar... ¿pero segundos??? ¿¿¿en realidad???

Answer

Sé que esta es una publicación antigua, pero si (como lo hice yo) las personas están buscando una solución a través de innumerables foros para ahorrar tiempo al ingresar hojas de horas y no tener que ingresar manualmente "Dos puntos", pero tener los dos puntos visibles y luego ser capaz de realizar cálculos... todo sin usar VBA Script, celdas ocultas y fórmulas... etc... etc... Esta es la solución que se me ocurrió, y parece funcionar con los principales ceros, horario de 24 horas y hojas de horas que abarcan la medianoche (aunque sería necesario realizar ajustes si espera que una hoja de horas abarque varias medianoches; no vi que esto fuera necesario en mi caso).

Además, no necesito "Segundos", pero deberías poder expandirlo con bastante facilidad si sigues el concepto:

Formato personalizado de las celdas de hora de inicio y hora de finalización como:

0#":"##

Esto las convierte en "celdas de texto", pero rellena los dígitos para mostrar siempre cuatro dígitos (incluso con un cero al principio o un cero para los amigos estadounidenses).

Tenga en cuenta que esto permitirá que la entrada de CUALQUIER número de cuatro dígitos parezca un formato de hora. Entonces, en teoría, es posible ingresar a las 0768 y convertirse en la hora 07:68, que por supuesto no existe. Así que tampoco

a) cree una lista de validación de datos de la siguiente manera: - en una hoja de trabajo separada (pestaña), en una sola columna, cree una lista de todos los valores aceptables. En mi caso, esto fue desde las 0500, 0515, 0530, 0545.....0445 ya que solo estaba tratando con incrementos de 15 minutos. Si necesita profundizar en segundos (¿en serio?), esto sería bastante largo... es decir. 050001, 050002... etc. O puede escribir otra fórmula solo para crear esta lista y luego copiar los datos de texto (pegar solo valores) en una nueva columna para eliminar las fórmulas (yo lo haría). - Seleccione todas estas celdas de "valores aceptables) y en la pestaña "Forumulas" de la barra de herramientas seleccione "Definir nombre". Asígnele un nombre como "MonkeyTimeList". - Vuelva a su hoja de trabajo de horas, seleccione todas las celdas que cubren ambos inicios. y horas de finalización, y vaya a la pestaña "Datos" de la barra de herramientas, seleccione "Validación de datos>Validación de datos>Configuración", en la sección "Permitir", seleccione "Lista" en el menú desplegable, luego en la sección "Fuente" escriba en el nombre que definiste precedido por "=", entonces en mi caso "=MonkeyTimeList". Antes de hacer clic en "Aceptar", seleccione la pestaña "Alerta de error", marque la casilla "Mostrar alerta de error...", y en el En el menú desplegable "Estilo", seleccione "Detener". Siéntase libre de agregar un mensaje de error como "Monkey Man dice que es un idiota, intente ingresar los tiempos en el FORMATO CORRECTO". Luego haga clic en "Aceptar". de su lista "aceptable" se puede ingresar como una hora.

b) en teoría, podría usar el formato condicional solo para resaltar una celda donde se agregan datos incorrectos. Esto evitaría el uso de una hoja de trabajo separada para sus datos de validación de datos. No lo he hecho así, por eso digo teóricamente. Ese puede ser un tutorial diferente....

Entonces, ahora puede ingresar sus tiempos, sin tener que buscar los dos puntos y la tecla Mayús cada vez.

Si REALMENTE quieres segundos en tu formato de celda, entonces el formato de celda personalizado se vería así:

0#":"##":"##

ASÍ QUE... AHORA PARA CALCULAR LAS HORAS desde la hora de inicio hasta la hora de finalización... nuevamente, esto se basa solo en minutos... pero si puedes seguir la lógica, también se puede expandir a segundos.

En la tercera columna (suponiendo que la columna A es la hora de inicio, B es la hora de finalización y C es el total de horas), ingrese la fórmula:

=SI(VALOR(B2)>=VALOR(A2),(SUM(VALOR(MEDIO(TEXTO(B2,"0000"),1,2)),VALOR(MEDIO(TEXTO(B2,"0000"),3 ,2))/60))-(SUM(VALOR(MEDIO(TEXTO(A2,"0000"),1,2)),VALOR(MEDIO(TEXTO(A2,"0000"),3,2))/ 60)),(SUM(VALOR(MEDIO(TEXTO(B2,"0000"),1,2)),24,VALOR(MEDIO(TEXTO(B2,"0000"),3,2))/60)) -(SUM(VALOR(MEDIO(TEXTO(A2,"0000"),1,2)),VALOR(MEDIO(TEXTO(A2,"0000"),3,2))/60)))

Ahora a explicarlo...

La función "IF" distingue las horas de finalización en un reloj de 24 horas que son antes de medianoche y aquellas que son después de medianoche. SI es después de la medianoche, se agregan 24 horas a la hora de finalización para permitir que el cálculo de la hora de finalización menos la hora de inicio siga funcionando. SI es antes de medianoche, no es necesario sumar 24 horas. Entonces, para fines de cálculo, la hora 0100 en realidad se trata como 2500, 0200 es 2600 y así sucesivamente. NOTA. esto supone que un turno no dura más de 23 horas y 45 minutos. Si dura más, tal vez necesites factorizar las columnas de fecha en tu fórmula... repito, no lo necesitaba.

Las funciones MID funcionan en TEXTO (de ahí la función TEXTO) y devuelven los dígitos en ubicaciones específicas de una celda referenciada, en el formato especificado... Así que, "MID(TEXTO(B2,"0000"),1,2) "mira la celda B2, y siempre la mira en el formato de 4 dígitos/letras, y devuelve los dos dígitos comenzando en la posición "1". En mi caso esto representa horas enteras. Otro ejemplo: "MID(TEXT(A2,"000000"),5,2)" buscaría en la celda A2 y devolvería los dos dígitos comenzando en la posición "5", que podrían ser segundos completos.

Coloqué la función "VALOR" delante de todo esto para convertir el texto devuelto en un número, de modo que podamos trabajar con él en los cálculos.

Entonces, esto nos permite para cada celda tratar las horas, los minutos y los segundos por separado. Los cálculos con horas son sencillos ya que ya son números enteros. Sin embargo, los minutos son en realidad fracciones de horas, o fracciones de 60 minutos, por lo que una vez que extraigas los dígitos que representan "minutos", divídelos por "60" para obtener una "fracción" de una hora. Al igual que con los segundos, divida por 3600 (número de segundos en una hora) para obtener los segundos como una "fracción de una hora".

Ahora que los tiempos están representados como números con fracciones, puedes sumar o restar como quieras. En el caso de mi fórmula, dará el número total de horas entre la hora de inicio y la hora de finalización. Formateo la celda con la fórmula como Personalizado> 00.00, lo que luego me permite multiplicar por tarifas por hora, etc.

Si alguien todavía quiere segundos en su fórmula. y te quedas atascado, házmelo saber. Si estoy aburrido en el trabajo, tal vez pueda ayudar... ¿pero segundos??? ¿¿¿en realidad???

Ingrese la hora sin dos puntos (hhmmss) y calcule las diferencias horarias en Excel

Respuesta1

Respuesta2

Respuesta3

información relacionada