Manejar valores de tiempo en Excel puede llegar a ser complicado debido a los formatos que tenemos que utilizar para que el programa los interprete adecuadamente y podamos expresarlos de la forma en la que queremos. Antes de empezar a trabajar con horarios debemos tener claro cómo funciona el tiempo en Excel para no tener problemas con nuestros cálculos.
Lo primero que debemos saber es que las horas en Excel se manejan como decimales. Esto quiere decir que Excel fracciona las 24 horas de un día para hacer sus cálculos por lo que un decimal como 0.77, aunque no tenga sentido para nosotros, Excel lo entenderá como 6:24 pm. Esto puede llegar a ser confuso, pero solo debemos tener presente de dónde sale el cálculo de Excel para no confundirnos y poder seleccionar un formato más cómodo para visualizar el valor.
Entonces, si Excel nos presenta los valores de horario en forma de decimales lo que más nos interesa es convertirlo a un formato más amigable para nosotros. Para esto solo tenemos que seleccionar la celda en la que se encuentra nuestro decimal y presionar en nuestro teclado Ctrl + 1, una vez hecho esto se abrirá una ventana desde la cual podremos formatear nuestros decimales de la forma en que queramos. Excel inclusive nos presenta una categoría específica para el tiempo.
Preferimos utilizar la categoría de “Personalizada” para escribir el formato adecuado para nuestra hora (hh:mm:ss). Una vez aplicamos este tipo de formato obtendremos nuestra hora en formato de 24 horas.
Otra cosa que debemos tomar en cuenta cuando trabajamos con tiempo en Excel es que, así como las horas están comprendidas en decimales, los días están definidos en números. Para ser más específicos respecto a esto, Excel enumera los días desde el 1/1/1900. Esto quiere decir que si escribimos un numero aleatorio y le damos el formato adecuado nuestro software contará cuantos días han pasado desde la fecha anteriormente mencionada hasta el número que escribimos. Si por alguna razón queremos trabajar con una fecha anterior al año 1900 Excel no la reconocerá como tal, podemos darnos cuenta de si nuestra información está formateada correctamente si se ubica del lado derecho de la celda, de permanecer en el lado izquierdo después de escribir la información significará que Excel interpreta esos valores como datos cualesquiera. Uno de los formatos que podemos utilizar para convertir números a fecha es aa/mm/dd.
Para ver cómo funciona el tema de los horarios en Excel tomaremos el siguiente ejemplo. Tenemos una tabla con la información de una línea de taxis con la hora de salida y de llegada de cada unidad. Con estos datos trataremos de responder tres preguntas:
- ¿Cuánto tiempo trabajó en total cada quién?
- ¿Cuántas horas en promedio por día?
- ¿Cuál de los taxis trabajó por más tiempo?
Para calcular cuánto trabajó cada uno, por lógica solo deberíamos restar la hora de llegada menos la hora de salida para obtener la cantidad de horas total. Pero al restar las casillas podemos obtener un resultado similar al siguiente.
Como explicamos con anterioridad a pesar de que realizamos una operación con fechas formateadas correctamente el resultado que obtenemos es un numero decimal. Es aquí donde entran en juego los conocimientos que describimos. En realidad, este decimal es la respuesta que buscamos, solo tenemos que usar Ctrl + 1 para convertirlo en el valor deseado. Después de aplicarle un formato de hh:mm a nuestro decimal obtendremos que el primer taxi de nuestra lista laboro una cantidad de 23:16 horas en total.
Entonces perfecto, ya tenemos la respuesta a nuestra primera incógnita. Como queremos procesar estos datos para obtener más información supongamos que añadimos una tabla dinámica para que nos de las respuestas que buscamos.
Si movemos entonces “Taxi” a filas y “Horas trabajadas” a valores podemos encontrarnos con otro inconveniente. Nuestra tabla dinámica está procesando la información de nuestra tabla de una forma distinta a la que queremos, en este caso nos está dando la cantidad de veces que trabajó cada taxi cuando en realidad queremos saber cuantas horas en total trabajaron. Para hacer esto solo tenemos que cambiar la configuración de nuestro campo de valor en la tabla y cambiarlo de cuenta a suma.
Una vez hacemos esto nuestra tabla sumará el total de todas las horas trabajadas por los taxistas y nos dará un resultado como este:
No obstante, si al ver esta tabla pensamos que el Azote de Tlajomulco trabajó un total de 71.11 horas estamos equivocados. Este es el tipo de situaciones en la que debemos prestar mucha atención al trabajar con duraciones. Como especificamos al principio Excel identifica las horas como fracciones de un día, esto quiere decir que los valores de nuestra tabla no son horas, sino días. Como nosotros ya sabemos la forma en la que Excel interpreta estos datos no tenemos problemas para comprender de dónde sale este número, pero si le presentamos esta tabla a alguien más probablemente generemos confusión. Afortunadamente podemos seleccionar las cantidades de la tabla y utilizar Ctrl + 1 para formatearlas.
Si formateamos estos valores con hh:mm como veníamos haciendo probablemente nos encontremos con un error. Esto sucede debido a que Excel toma los valores presentados muy literalmente así que en este caso en el que sabemos que tenemos una duración de más de 70 días entonces debemos introducir un formato acorde a nuestros valores para que Excel pueda identificarlo apropiadamente.
Con esto nos referimos a que si utilizamos los formatos que venimos utilizando Excel no nos entenderá adecuadamente, para encontrar la respuesta a nuestra incógnita solo debemos utilizar el formato [h]:mm:ss con el pequeño detalle de encerrar la h entre Brackets Excel comprenderá efectivamente que queremos la sumatoria de la cantidad de horas total.
Por otro lado, si queremos que nuestro tiempo esté comprendido en días en vez de horas totales seguir el mismo procedimiento no nos funcionará, el formato de encerrar el valor deseado entre Brackets solo funciona con horas, por lo que solo nos queda la opción de hacer cálculos por nuestra cuenta con los conocimientos aprendidos para convertir las horas totales en días.
Como podemos apreciar, Excel nos proporciona las herramientas necesarias para poder trabajar con formatos de duración, pero siendo sinceros es un área de trabajo que Excel podría mejorar para hacer más sencillo su manejo. No obstante, dentro de Excel podemos encontrar otra alternativa para trabajar con horarios de una forma mucho más sencilla.
Con Power Query podemos encontrar soluciones mucho más fáciles a la hora de procesar datos de tiempo. Siguiendo el mismo ejemplo de la tabla que venimos utilizando como ejemplo solo tenemos que dirigirnos a cualquier celda que contenga nuestros datos y presionar Ctrl + T para hacer que Excel convierta nuestros datos en una tabla.
Aparecerá una nueva ventana que reconocerá automáticamente las celdas que conformaran nuestra tabla y haremos clic en Ok. Una vez hecho esto nos dirigimos al menú superior de Excel y buscamos la pestaña de “Datos”. Allí haremos clic en el botón de “desde una tabla o rango” para exportar nuestros datos a Power Query.
Una vez exportamos nuestros datos se abrirá Power Query con los datos de nuestra tabla. Esta herramienta integrada a Excel tiene dentro de las pestañas de su menú superior “Añadir Columna”, dentro de esta pestaña podemos observar la posibilidad de añadir columnas que procesen datos de fecha, tiempo y duración.
Si quisiéramos saber nuevamente cuántas horas trabajó cada uno solo debemos seleccionar nuestra columna de “Hora Llegada”, presionar la tecla Ctrl y seleccionar la columna de “Hora Salida”. Una vez hecho esto nos vamos a al botón de “Tiempo” en el menú y seleccionamos restar.
El orden en el que seleccionamos nuestras columnas es relevante ya que al utilizarlas para hacer una sustracción el orden en el que introduzcamos los valores puede cambiar nuestro resultado.
De esta forma Power Query nos da la información requerida sin tantos problemas como Excel, al obtener nuestra nueva columna podemos ver que la información es reconocida como duración. Esto hace que podamos seleccionar el botón de “Duración” en el menú superior para obtener un abanico de opciones mucho más amplio para procesar nuestros datos fácilmente.
En este caso particular estamos buscando el total de horas, que podemos seleccionar cómodamente de entre las numerosas opciones que tenemos para procesar los datos. Una vez seleccionamos la opción una nueva columna se añadirá a nuestra tabla con la información indicada.
Después de que tenemos los datos buscados solo queda dirigirnos a la pestaña de “Inicio” en el menú superior y seleccionar el botón de “Cerrar y Cargar”. Esto cargará nuestra tabla en una nueva pestaña de nuestro libro de trabajo y solo nos faltará insertar una tabla dinámica para poder procesar nuestros datos fácilmente y para conseguir un resultado exactamente igual al que obtuvimos con Excel, pero con más facilidad y rapidez.
A pesar de que es un poco complicado trabajar con valores de tiempo dentro de Excel podemos conseguir solución a nuestras necesidades siempre y cuando tengamos los conocimientos necesarios para saber de qué forma son interpretados los formatos por Excel. Independientemente de qué herramienta prefiramos para esto, ya sea directamente Excel o Power Query podremos obtener los resultados que buscamos sin ningún problema.
Deja una respuesta