Trabajando con fechas y horas

En Excel, la fecha y las horas son números y por lo tanto se puede hacer operaciones con ellos.

Para Excel un numero representa, en su parte entera un día, y en su parte decimal unas horas.

Vamos a hacer algunos ejercicios para verificar esto.

En una hoja nueva escribimos una fecha, por ejemplo 1-1-16 y debajo, hacemos referencia a esa celda.

=+B1

Todo es igual, hay dos fechas, en dos celdas. Pero a la celda de abajo le vamos a poner formato numérico y quedara

Vamos a añadir algunos días a la fecha.

En otra celda ponemos los días a sumar, por ejemplo 10

Y en el resultado escribimos la formula

=+B1+B3

Lógicamente nos da 11-1-16

Ahora vamos a restar días a la fecha inicial. Ponemos los días a restar, por ejemplo 1, y debajo la operación que apunte a la fecha inicial

=+B1-B5

Obtenemos el día antes

Así pues está claro que las fechas son días y eso nos permite hacer operaciones entre ellas: sumar días, restar días.

Por ejemplo vamos a restar de la primera fecha, la última que hemos puesto, nos debería dar un día

=+B1-B6

Pero Excel nos muestra

Una fecha, esto es porque intenta ayudarnos poniendo el formato de arriba, formato fecha. Pongamos a esta celda formato número y debe salir 1

Esto nos ha dado una pista, el primer día para Excel es el 1-1-1900

Vamos a verificarlo poniendo en una celda 1, y debajo apuntamos a esta misma celda, pero le ponemos formato fecha

Está claro, el primer día es el 1-1-1900.

Vamos a ver qué pasa si restamos a 1-1-1900 un día, debería dar 31-12-1899

Ponemos un 1 y debajo la formula

=+B10-B11

Nos da 00/01/1900

Excel no sabe operar con fechas más allá del 1/1/1900

Probemos con las horas

En una celda ponemos las 12:00 y debajo hacemos referencia a ella

=+E1

Y a esta celda le ponemos formato número, nos da

Así pues las 12:00, que es medio día, es el 0,5

Un caso especial es la suma de horas, el formato parece ser que no es correcto, pero vamos a ver la solución

Supongamos que tenemos una tabla con las horas trabajadas en distintos días, y queremos hacer la suma

Como sabemos usamos la función SUMA poniendo

=SUMA(E5:E8)

Y nos da

Solo hemos trabajado 6:25 horas ¿??

Es un problema de formato. Hay que ir a Formato, desplegar en Personalizada, elegir más formatos de número y buscar el que pone

[h]:mm:ss

Al aceptar, las horas quedan bien representadas, hemos trabajado 30:25 horas

Un dato interesante es como convertir las horas en formato hora, a horas en formato número centesimal.

Basta multiplicar las horas por 24, poniendo por ejemplo

=+E5*24

Y si arrastramos nos da

Efectivamente como sabemos las 7:30 son las 7 y media, o sea, 7,5

Y la suma también es un número

Otra cuestión es poder sumar meses o años a una fecha. Para ello debemos descomponer una fecha en sus partes, sumar valores y volver a recomponer la fecha

Lo primero descomponer la fecha en su partes, con las funciones DIA(), MES() y AÑO()

Pongamos la fecha en una celda y debajo la descomponemos en día, mes y año

Hemos puesto las siguientes formulas

Par el día: =DIA(B14)

Para el mes: =MES(B14)

Para el año: =AÑO(B14)

Para la reconstrucción: =FECHA($D$16;$C$16;$B$16)

Los hemos marcado con el $ usando la F4 pues los vamos a usar en las filas de abajo

La función FECHA es una función que a partir de tres parámetros, tres valores: el año, el mes y el día, en ese orden, nos devuelve una fecha

Ahora decidimos que queremos sumar, montando un cuadro por ejemplo

Para obtener los resultados ponemos

Para sumar días: =FECHA($D$16;$C$16;$B$16+B17)

Para sumar meses: =FECHA($D$16;$C$16+C18;$B$16)

Para sumar años: =FECHA($D$16+D19;$C$16;$B$16)

Para sumar años, meses y año: =FECHA($D$16+B20;$C$16+C20;$B$16+D20)

Observemos que a las partes correspondientes, le sumamos la celda que queremos sumar.

Esto nos da

Al sumar un día, obtenemos el 1-3

Al sumar un mes, obtenemos el 29-3. No sumamos 30 días (nos daría 30-3), sino un mes entero.

Al sumar un año, nos da 1-3-17, pues el 2017 no es bisiesto y no existe el 29-3

Así pues para sumar meses o años, debemos descomponer una fecha y usar la función FECHA para recomponer.

Para sumar días basta una suma directa 29-2-16 + 30 días, nos da 30-3-16

Aqui esta el ejercicio Funciones de fecha y hora

2 comentarios sobre “Trabajando con fechas y horas”

  1. Hola, Tengo un problema, al armar mi tabla de fechas, al crear una columna
    Fecha1 = CALENDAR (MIN ( PayU[Fecha Creacion] );MAX ( PayU[Fecha Creacion] )), me muestra el resultado con la hora en 0. (12/12/2018 00:00:00), a un que he modificado el formato por todo lados, incluso usando la función Format no funcionada para nada.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.