Transcripción del video
Las fechas son uno de los tipos de datos más comunes en Excel y frecuentemente tendremos la necesidad de obtener alguna de sus partes para trabajar con ella y realizar algún otro cálculo.
Pero, si no sabemos cómo trabajar adecuadamente con las fechas, nos enfrentaremos con muchos errores o tendremos resultados incorrectos.
Comenzaré diciendo que, una gran cantidad de usuarios de Excel cree que las fechas son cadenas de texto debido a la manera en que se muestran en pantalla.
Por ejemplo, en la columna Fecha tenemos toda una lista de fechas que muestran el nombre de la semana, el día, el mes y el año.
Supongamos que nos han pedido extraer el nombre de la semana de cada una de las fechas, para la primera fecha tendríamos que extraer el día lunes, para la segunda fecha el martes, luego el sábado, etc.
Si las fechas fueran cadenas de texto, podríamos usar la función izquierda de la siguiente manera.
En la celda B2 ingresaré el símbolo igual y la función Izquierda y como primer argumento la referencia a la celda A2 que tiene la cadena de texto que vamos a evaluar.
El segundo argumento es la cantidad de caracteres que vamos a extraer y en el caso del día lunes son 5 caracteres así que ingresaré ese número y cerraré el paréntesis.
La sorpresa viene cuando pulsamos la tecla Entrar y en lugar de la cadena de texto lunes obtenemos un número.
Algunos podrían pensar que esto es un error, pero en realidad olvidan que las fechas en Excel son valores numéricos.
Basta con seleccionar la celda A2 y en la lista de formatos elegir la opción General y observaremos el mismo número devuelto por la función IZQUIERDA.
Aunque las fechas en Excel son números, podremos utilizar un conjunto de funciones para extraer información importante de las fechas y una de ellas será la función TEXTO.
Esta función tiene dos argumentos.
El primero de ellos es el valor que vamos a evaluar y el segundo argumento es el formato que queremos aplicar a ese valor.
En esta clase revisaremos los formatos que nos ayudarán a obtener información de una fecha.
Antes de crear la fórmula, pulsaré el botón Deshacer para regresar el formato de fecha a la celda A2 y borraré la fórmula de la celda B2 e ingresaré la función TEXTO.
Su primer argumento será la referencia a la celda A2 que contiene la fecha que vamos a evaluar, y su segundo argumento será el formato que vamos a aplicar y que será la letra “d” encerrada en comillas dobles.
Al pulsar le tecla Entrar obtenemos el número 14 que es el número de día de la fecha de la columna izquierda.
Haré doble clic en el controlador de relleno de la celda para copiar la fórmula hacia abajo y tendremos el resultado para todas las fechas.
Este ha sido el primero de los formatos que vamos a revisar y avanzaremos uno a uno hasta llegar al formato que nos devolverá la cadena de texto del día.
Para continuar voy a copiar el texto de la fórmula con el atajo Ctrl+C y activaré la celda C2 pero en esta ocasión modificaré el formado para que tenga dos letras “d” y pulsaré la tecla Entrar.
De nuevo obtenemos el número 14 y en este momento no podemos ver la diferencia con el formato de la izquierda, pero al copiar la fórmula hacia abajo podrás observar que se ha insertado un número cero a la izquierda de esos días con un solo dígito.
Por lo tanto, el formato de dos letras “d”, le indica a Excel que use un formato de dos dígitos para los días.
Continuemos con el siguiente formato.
Activaré la celda D2 y de nuevo pegaré el texto de la fórmula y en esta ocasión ingresaré una tercera letra “d” en el formato indicado en la función TEXTO y al pulsar Entrar obtendremos las primeras tres letras del nombre del día.
Copiaré la fórmula hacia abajo y verás las primeras tres letras de todos los días de la semana que le corresponden a las fechas de la primera columna.
Estoy seguro que ya sospechas cuál será el último formato que utilizaremos el cual nos ayudará a obtener el nombre completo del día.
Activaré la celda E2 y pegaré la fórmula e ingresaré 4 letras “d” y al pulsar la tecla Entrar obtendremos el nombre del día.
Copiaré la fórmula hacia abajo y tendremos los nombres de día para todas las fechas.
Tal como lo puedes notar en la barra de fórmulas, la única diferencia que tenemos entre estas fórmulas, es la cantidad de letras d que hemos indicado en el formato de cada una de ellas.
Para obtener información sobre el mes también podemos utilizar la función TEXTO, pero en lugar de utilizar la letra “d” usaremos la letra “m”.
En la celda F2 pegaré la fórmula que hemos utilizado anteriormente y reemplazaré la letra “d” por la letra “m”.
Al ingresar esta fórmula obtendremos el día del mes de la fecha
Voy a copiar el texto de esta fórmula y en seguida activaré la celda G2 y pegaré la fórmula, pero ingresaré una segunda letra “m” y puedes ver que obtenemos dos dígitos para el número de mes.
En la celda H2 pegaré la fórmula y la editaré para que el formato tenga tres letras “m” y obtendremos los primeros tres caracteres del mes.
Finalmente pegaré la fórmula en la celda I2 y colocaré el formato con 4 letras “m” y en esta ocasión obtenemos el nombre completo del mes.
Seleccionaré el rango de celdas F2:I2 y copiaré las fórmulas hacia abajo para obtener los resultados de todas las fechas.
Como has observado en estas cuatro columnas he utilizado la letra “m” para obtener la información del mes.
Para finalizar, utilizaré la función TEXTO para obtener información del año.
En la celda J2 pegaré nuestra fórmula, y reemplazaré la letra “m” por dos letras “a” y en este caso, obtendremos los últimos dos dígitos del año.
Para obtener los cuatro dígitos del año, en la celda K2 pegaré la fórmula y editaré el formato para obtener cuatro letras “a” y al pulsar la tecla Entrar obtendré los cuatro dígitos del año.
Seleccionaré las dos celdas con las fórmulas que acabamos de crear y las copiaré hacia abajo y tendremos la información del año para todas las fechas.
En caso de que tuvieras Excel en inglés, deberás utilizar la letra “y” para indicar el formato del año en lugar de utilizar la letra “a”.
De esta manera podemos utilizar la función TEXTO para obtener información de una fecha y lo único que hemos cambiado es el formato que hemos indicado en el segundo argumento de la función.
Excel tiene las funciones día, mes y año, y vamos a probarlas para comparar sus resultados con los devueltos por la función TEXTO.
En la celda M2 ingresaré el símbolo igual y la función DIA y evaluaré la fecha de la celda A2.
En la celda N2 ingresaré la función MES y la referencia a la celda A2 y finalmente en la celda O2 ingresaré la función AÑO y la referencia a la celda A2.
Seleccionaré todas las celdas y las copiaré hacia abajo para obtener los resultados para todas las fechas y podemos comprobar que los resultados de la columna M son idénticos a los valores de la columna B.
Los resultados de la columna N son iguales a los valores de la columna F y el valor devuelto por la función AÑO es igual a los valores de la columna K.
No existe una ventaja de una función sobre otra, pero puedes notar que la función TEXTO nos permite obtener toda la información de una fecha con solo cambiar el tipo de formato.
Para nuestro siguiente ejemplo activaré la Hoja2 del libro y puedes observar una columna donde tenemos fechas y horas en la misma celda.
Si las fechas en Excel son números enteros, las horas serán decimales y eso lo podemos comprobar al activar alguna de las celdas de la columna A y en la lista de formatos elegimos la opción General y puedes notar que tenemos un decimal que equivale a la hora indicada en la celda.
Pulsaré el botón Deshacer y a continuación vamos a crear unas fórmulas que nos ayudarán a separar la fecha de la hora.
Debes recordar que este cálculo se basa en el hecho de que estamos trabajando con números.
En la celda B2 ingresaré la función ENTERO que solo tiene un argumento así que ingresaré la referencia a la celda A2 que tiene el valor que vamos a evaluar.
La función ENTERO redondea un número hacia abajo, es decir, hacia el entero inferior más próximo y en nuestro ejemplo eso significará que se removerán los decimales.
Cerraré el paréntesis de la función y al pulsar le tecla Entrar obtenemos la fecha con las cero horas.
En un instante mostraré como podemos dejar de ver las horas, pero antes copiaré la fórmula hacia abajo y en seguida, en la lista de formatos, elegiré la opción Fecha corta y de esa manera observaremos solo las fechas.
La parte más interesante de este cálculo es obtener la parte decimal, que es la información de la hora, y para eso te mostraré dos métodos.
En la celda C2 ingresaré el símbolo igual y la referencia a la celda A2, que es el número que incluye la parte entera y los decimales.
En seguida ingresaré el símbolo de resta y vamos a restar la parte entera así que ingresaré la función ENTERO y la referencia a la celda A2.
Esta fórmula está restando la parte entera del número original en la celda A2 y por lo tanto nos quedaremos solo con la parte decimal.
Pulsaré la tecla Entrar y la celda nos muestra un decimal, copiaré la fórmula hacia abajo y para ver estos datos como una hora solo debo cambiar el formato y lo puedo hacer desde la lista de formatos y eligiendo la opción hora.
Puedes comprobar que tenemos las mismas horas indicadas en la primera columna solo que en un formato de 12 horas.
En un instante te mostraré cómo modificar ese formato, pero antes vamos a crear otra fórmula que también nos ayudará a extraer la hora de los datos en la primera columna.
En la celda D2 ingresaré el símbolo igual y la función RESIDUO que nos ayuda a obtener el residuo de una división y como primer argumento ingresaré la referencia a la celda A2 y como segundo argumento ingresaré el número 1 que será el divisor y de esa manera el residuo será la parte decimal de la celda A2.
Pulsaré la tecla Entrar y veremos una fecha con el día cero de enero de 1900 pero eso se debe a que solo tenemos un número decimal en la celda.
Para ver la hora, iré a la lista de formatos y elegiré la opción Hora y en seguida copiaré la fórmula hacia abajo.
Puedes notar que tenemos los mismos resultados de la columna de la izquierda, pero vamos a cambiar el formato para que se muestre tal como las horas de la columna A.
Haré clic derecho sobre las celdas y elegiré la opción Formato de celdas.
Activaré la categoría Personalizada y elegiré un formato que muestre solo las horas y los minutos excluyendo los segundos.
Pulso el botón Aceptar y veremos los datos en el formato de 24 horas.
En este ejemplo hemos trabajado con una celda que tiene la fecha y la hora y es importante que conozcas el comportamiento predeterminado de Excel al momento de sumar este tipo de dato.
Activaré la Hoja3 del libro donde tenemos de nuevo la columna de fechas y horas y a cada una de estas celdas le sumaremos las horas indicadas en la columna B.
Activaré la celda C2 e ingresaré la función SUMA y como argumentos ingresaré el rango A2:B2 y como resultado obtendremos el día 15 de enero.
Observa que la fecha original es el 14 de enero a las 12:30 horas, pero al sumar 18 horas, Excel aumenta automáticamente el día para dejarnos en el 15 de enero a las 6:30 horas.
Copiaré la formula hacia abajo y podrás notar que en todos los casos en los que la suma de horas excede las 24 horas, tendremos un aumento en el día de la fecha.
Este comportamiento es muy útil al momento que trabajamos con fechas y horas, pero existen algunos casos muy particulares donde queremos evitarlo y te lo mostraré en la Hoja4.
La columna D nos muestra las horas que ha trabajado una persona durante toda la semana y queremos hacer la suma de esas horas en la celda D8.
Antes de crear la fórmula, seleccionaré el rango de la columna Horas y en la barra de estado podemos ver que la suma de estas celdas nos da 45 horas y 15 minutos.
Activaré la celda D8 e ingresaré la función SUMA y como argumento ingresaré el rango D2:D6 y al pulsar Entrar obtendremos el total de 21 horas y 15 minutos lo cual es claramente erróneo ya que esperábamos ver el total de 45 horas y 15 minutos.
Como lo vimos en la hoja anterior, cada vez que Excel suma 24 horas, aumenta el número de día y en este caso, las 45 horas y 15 minutos del rango que estamos sumando, equivale a 1 día completo más 21 horas y 15 minutos que son los que observamos en este momento en pantalla.
Para evitar este comportamiento y pedir a Excel que nos muestra la suma total de horas, debemos cambiar el formato de la celda.
Haré clic derecho en la celda D8 y elegiré la opción Formato de celdas y en la categoría Personalizada debemos buscar un formato que tenga la letra “h” encerrada en corchetes.
Desde el momento en que seleccionamos este formato, puedes ver en la sección Muestra que se reflejan las 45 horas y 15 minutos.
Voy a modificar el formato para remover los segundos ya que en este caso no me interesan y finalmente pulsaré el botón Aceptar.
Con este cambio de formato le hemos pedido a Excel que muestre el total de las horas que hemos sumado sin importar que dicha suma sobrepase las 24 horas.
Espero que los ejemplos que hemos visto en esta clase te ayuden a comprender la manera en que funcionan las fechas y las horas en Excel y puedas trabajar de una mejor manera con este tipo de dato.