Tutorial de Excel: Cálculo de fechas y horas (calcular diferencia, edad, sumar/restar)
En Excel, los cálculos con fechas y horas son muy habituales: por ejemplo, para hallar la diferencia entre dos fechas u horas, sumar o restar valores de fecha y hora, calcular la edad a partir de una fecha de nacimiento, etc. Este tutorial recopila prácticamente todos los escenarios relacionados con estos cálculos y ofrece los métodos correspondientes.
En este tutorial, he preparado algunos ejemplos para ilustrar los métodos; puede ajustar las referencias según sus necesidades al usar el código VBA o las fórmulas que aparecen a continuación.
1. Calcula la diferencia entre dos fechas u horas.
Calcular la diferencia entre dos fechas u horas es, probablemente, la tarea más común al trabajar con fechas y horas en Excel. Seguir los ejemplos que se ofrecen a continuación le ayudará a mejorar su eficiencia al enfrentarse a este tipo de situaciones.
1,11 Calcular la diferencia entre dos fechas en días/meses/años/semanas
La función DATEDIF de Excel te permite calcular rápidamente la diferencia entre dos fechas en días, meses, años o semanas.
Haga clic para obtener más información sobre la función DATEDIF.
Diferencia en días entre dos fechas
Para obtener la diferencia en días entre dos fechas situadas en las celdas A2 y B2, utilice la siguiente fórmula:
=DATEDIF(A2,B2,«d»)
Pulse la tecla Intro para obtener el resultado.
Diferencia en meses entre dos fechas
Para obtener la diferencia en meses entre dos fechas situadas en las celdas A5 y B5, utilice la siguiente fórmula:
=DATEDIF(A5,B5,«m»)
Pulse la tecla Intro para obtener el resultado.
Diferencia en años entre dos fechas
Para obtener la diferencia en años entre dos fechas situadas en las celdas A8 y B8, utilice la siguiente fórmula:
=DATEDIF(A8,B8,«y»)
Pulse la tecla Intro para obtener el resultado.
Diferencia en semanas entre dos fechas
Para obtener la diferencia en semanas entre dos fechas situadas en las celdas A11 y B11, utilice la siguiente fórmula:
=DATEDIF(A11,B11,«d»)/7
Pulse la tecla Intro para obtener el resultado.
Nota:
1) Al utilizar la fórmula anterior para obtener la diferencia en semanas, es posible que el resultado aparezca con formato de fecha; en ese caso, aplique al resultado el formato General o Número según sus necesidades.
2) Al utilizar la fórmula anterior para obtener la diferencia en semanas, es posible que el resultado sea un número decimal. Si desea obtener un número entero de semanas, puede añadir la función REDONDEAR.MENOS al principio, tal como se muestra a continuación, para obtener la diferencia entera en semanas:
=ROUNDDOWN(DATEDIF(A11,B11,«d»)/7,0)
1,12 Calcular los meses ignorando los años y los días entre dos fechas
Si desea calcular únicamente la diferencia en meses entre dos fechas, ignorando los años y los días, como se muestra en la siguiente captura de pantalla, aquí tiene una fórmula que le será de gran ayuda.
=DATEDIF(A2,B2,«ym»)
Pulse la tecla Intro para obtener el resultado.
A2 es la fecha de inicio y B2 es la fecha de fin.
1,13 Calcular los días ignorando los años y los meses entre dos fechas
Si desea calcular únicamente la diferencia en días entre dos fechas, ignorando los años y los meses, como se muestra en la siguiente captura de pantalla, aquí tiene una fórmula que le será de gran ayuda.
=DATEDIF(A5,B5,«md»)
Pulse la tecla Intro para obtener el resultado.
A5 es la fecha de inicio y B5 es la fecha de fin.
1,14 Calcular la diferencia entre dos fechas y obtener años, meses y días
Si desea obtener la diferencia entre dos fechas expresada como «xx años, xx meses y xx días», tal como se muestra en la siguiente captura de pantalla, aquí tiene una fórmula que le será de gran ayuda.
=DATEDIF(A8, B8, «y») &« years, »&DATEDIF(A8, B8, «ym») &« months, » &DATEDIF(A8, B8, «md») &« days»
Pulse la tecla Intro para obtener el resultado.
A8 es la fecha de inicio y B8 es la fecha de fin.
1,15 Calcular la diferencia entre una fecha y hoy
Para calcular automáticamente la diferencia entre una fecha y la fecha actual, basta con reemplazar la fecha final en las fórmulas anteriores por HOY(). A continuación, se muestra un ejemplo que calcula la diferencia en días entre una fecha pasada y hoy.
=DATEDIF(A11,TODAY(),«d»)
Pulse la tecla Intro para obtener el resultado.
Nota: si desea calcular la diferencia entre una fecha futura y hoy, cambie la fecha inicial por HOY() y utilice la fecha futura como fecha final, tal como se muestra a continuación:
=DATEDIF(TODAY(),A14,«d»)
Tenga en cuenta que, en la función DATEDIF, la fecha inicial debe ser anterior a la fecha final; de lo contrario, se devolverá el valor de error #¡NUM!.
1,16 Calcular días laborables con o sin festivos entre dos fechas
A veces puede necesitar contar el número de días laborables entre dos fechas dadas, incluyendo o excluyendo los festivos.
En esta parte utilizará la función DIAS.LAB.INTL:
Haga clic en DIAS.LAB.INTL para conocer sus argumentos y su uso.
Contar días laborables incluyendo festivos
Para contar los días laborables incluyendo festivos entre dos fechas situadas en las celdas A2 y B2, utilice la siguiente fórmula:
=NETWORKDAYS.INTL(A2,B2)
Pulse la tecla Intro para obtener el resultado.
Contar días laborables excluyendo festivos
Para contar los días laborables entre dos fechas situadas en las celdas A2 y B2, excluyendo los festivos del rango D5:D9, utilice la siguiente fórmula:
=DIAS.LAB.INTL(A5;B5;1;D5:D9)
Pulse la tecla Intro para obtener el resultado.
Nota:
Las fórmulas anteriores consideran el sábado y el domingo como fines de semana. Si su definición de fin de semana es diferente, ajuste el argumento [fin_de_semana] según sus necesidades.
1,17 Calcular fines de semana entre dos fechas
Si desea contar el número de fines de semana comprendidos entre dos fechas, las funciones SUMAPRODUCTO o SUMA pueden ayudarle.
Para contar los fines de semana (sábados y domingos) entre dos fechas situadas en las celdas A12 y B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
O bien
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Pulse la tecla Intro para obtener el resultado.
1,18 Contar un día de la semana específico entre dos fechas
Para contar cuántas veces aparece un día de la semana específico, como el lunes, entre dos fechas, puede combinar las funciones ENTERO y DIASEM.
Las celdas A15 y B15 contienen las dos fechas entre las que desea contar los lunes. Utilice la siguiente fórmula:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Pulse la tecla Intro para obtener el resultado.
Cambie el número del día de la semana en la función DIASEM para contar un día diferente:
1 es domingo, 2 es lunes, 3 es martes, 4 es miércoles, 5 es jueves, 6 es viernes y 7 es sábado)
1,19 Calcular el Días restantes en un mes/año
A veces puede desear conocer el Días restantes en el mes o año a partir de una fecha dada, tal como se muestra en la siguiente captura de pantalla:
Obtener el Días restantes en el mes actual
Haga clic en EOMONTH para conocer sus argumentos y su uso.
Para obtener el Días restantes del mes actual en la celda A2, utilice la siguiente fórmula:
=EOMONTH(A2,0)-A2
Pulse la tecla Intro y arrastre el controlador de autorrelleno para aplicar esta fórmula a otras celdas, si es necesario.
Consejo: Los resultados pueden mostrarse en formato de fecha; cámbielos a formato general o numérico.
Obtener el Días restantes del año actual
Para obtener el Días restantes del año actual en la celda A2, utilice la siguiente fórmula:
=DATE(YEAR(A2),12,31)-A2
Pulse la tecla Intro y arrastre el controlador de autorrelleno para aplicar esta fórmula a otras celdas, si es necesario.
1,21 Calcular la diferencia entre dos horas
Para calcular la diferencia entre dos horas, aquí tiene dos fórmulas sencillas que le serán de gran ayuda.
Suponiendo que las celdas A2 y B2 contienen la hora de inicio y la hora de finalización respectivamente, use las siguientes fórmulas:
=B2-A2
=TEXT(B2-A2,«hh:mm:ss»)
Pulse la tecla Intro para obtener el resultado.
Nota:
- Si utiliza hora_fin - hora_inicio, puede dar formato al resultado con el formato de hora que necesite en el cuadro de diálogo Establecer formato de celda.
- Si utiliza TEXTO(hora_fin-hora_inicio;«formato_hora»), introduzca en la fórmula el formato de hora en el que desea que se muestre el resultado; por ejemplo, TEXTO(hora_fin-hora_inicio;«h») devuelve 16.
- Si la hora_fin es menor que la hora_inicio, ambas fórmulas devuelven valores de error. Para resolver este problema, añada ABS al principio de dichas fórmulas, por ejemplo: ABS(B2-A2) o ABS(TEXTO(B2-A2,«hh:mm:ss»)), y luego aplique formato de hora al resultado.
1,22 Calcular la diferencia entre dos horas en horas/minutos/segundos
Si desea calcular la diferencia entre dos horas en horas, minutos o segundos, como se muestra en la siguiente captura de pantalla, siga este apartado.
Obtener la diferencia en horas entre dos horas
Para obtener la diferencia en horas entre las horas de las celdas A5 y B5, utilice la siguiente fórmula:
=INT((B5-A5)*24)
Pulse la tecla Intro y, a continuación, formatee el resultado como número o con formato general.
Si desea obtener la diferencia en horas decimales, utilice (hora_final - hora_inicial) × 24.
Obtener la diferencia en minutos entre dos horas
Para obtener la diferencia en minutos entre las horas de las celdas A8 y B8, utilice la siguiente fórmula:
=INT((B8-A8)*1440)
Pulse la tecla Intro y, a continuación, formatee el resultado como número o con formato general.
Si desea obtener la diferencia en minutos decimales, utilice (hora_final – hora_inicial) × 1440.
Obtener la diferencia en segundos entre dos horas
Para obtener la diferencia en segundos entre las horas de las celdas A5 y B5, utilice la siguiente fórmula:
=(B11-A11)*86400)
Pulse la tecla Intro y, a continuación, formatee el resultado como número o con formato general.
1,23 Calcular únicamente la diferencia en horas entre dos horas (sin superar las 24 horas)
Si la diferencia entre dos horas no supera las 24 horas, la función HORA permite obtener rápidamente la diferencia en horas entre ambas.
Haga clic en HORA para obtener más detalles sobre esta función.
Para obtener la diferencia en horas entre las horas de las celdas A14 y B14, utilice la función HORA de la siguiente manera:
=HOUR(B14-A14)
Pulse la tecla Intro para obtener el resultado.
La hora de inicio debe ser anterior a la hora de finalización; de lo contrario, la fórmula devolverá el error #¡NUM!
1,24 Calcular únicamente la diferencia en minutos entre dos horas (sin superar los 60 minutos)
La función MINUTO extrae rápidamente solo los minutos de una hora, ignorando las horas y los segundos.
Haga clic en MINUTO para obtener más detalles sobre esta función.
Para obtener únicamente la diferencia en minutos entre las horas de las celdas A17 y B17, utilice la función MINUTO de la siguiente manera:
=MINUTE(B17-A17)
Pulse la tecla Intro para obtener el resultado.
La hora de inicio debe ser anterior a la hora de finalización; de lo contrario, la fórmula devuelve el error #¡NUM!
1,25 Calcular únicamente la diferencia en segundos entre dos horas (sin superar los 60 segundos)
La función SEGUNDO permite obtener rápidamente la diferencia exclusivamente en segundos entre ambas horas, ignorando las horas y los minutos.
Haga clic en SEGUNDO para obtener más detalles sobre esta función.
Para obtener únicamente la diferencia en segundos entre las horas de las celdas A20 y B20, utilice la función SEGUNDO de la siguiente manera:
=SECOND(B20-A20)
Pulse la tecla Intro para obtener el resultado.
La hora de inicio debe ser anterior a la hora de finalización; de lo contrario, la fórmula devolverá el error #¡NUM!
1,26 Calcular la diferencia entre dos horas y mostrarla en horas, minutos y segundos
Si desea mostrar la diferencia entre dos horas con el formato xx horas xx minutos xx segundos, utilice la función TEXTO tal como se muestra a continuación:
Haga clic en TEXTO para conocer los argumentos y el uso de esta función.
Para calcular la diferencia entre las horas de las celdas A23 y B23, utilice la siguiente fórmula:
=TEXT(B23-A23,«h»« hours »«m»« minutes »«s»« seconds»«»).
Pulse la tecla Intro para obtener el resultado.
Nota:
Esta fórmula también calcula únicamente la diferencia en horas sin superar las 24 horas, y la hora_final debe ser posterior a la hora_inicio; de lo contrario, devuelve el error #¡VALOR!
1,27 Calcular la diferencia entre dos fechas y horas
Si dispone de dos fechas y horas en el formato mm/dd/aaaa hh:mm:ss y desea calcular la diferencia entre ellas, puede usar una de las siguientes fórmulas según sus necesidades.
Obtener la diferencia entre dos fechas y horas y mostrar el resultado en formato hh:mm:ss
Tomando como ejemplo las fechas y horas de las celdas A2 y B2, utilice la siguiente fórmula:
=B2-A2
Pulse la tecla Intro para obtener un resultado en formato de fecha y hora, y luego formatee este resultado como [h]:mm:ss en la categoría personalizada de la pestaña Número del cuadro de diálogo Establecer formato de celda.
Obtener la diferencia entre dos fechas y horas y mostrarla en días, horas, minutos y segundos
Tomando como ejemplo las fechas y horas de las celdas A5 y B5, utilice la siguiente fórmula:
=INT(B5-A5) & « Days, » & HOUR(B5-A5) & « Hours, » & MINUTE(B5-A5) & « Minutes, » & SECOND(B5-A5) & « Seconds »
Pulse la tecla Intro para obtener el resultado.
Nota: en ambas fórmulas, la fecha_hora_final debe ser posterior a la fecha_hora_inicio; de lo contrario, las fórmulas devolverán un valor de error.
1,28 Calcular la diferencia horaria con milisegundos
En primer lugar, debe saber cómo dar formato a la celda para mostrar los milisegundos:
Seleccione las celdas en las que desea mostrar los milisegundos, haga clic con el botón derecho y elija Establecer formato de celda para abrir el cuadro de diálogo Establecer formato de celda. En la pestaña Número, seleccione Personalizada en la lista Categoría y escriba lo siguiente en el cuadro de texto: hh:mm:ss.000.
Utilice la fórmula:
Para calcular la diferencia entre las horas de las celdas A8 y B8, utilice la siguiente fórmula:
=ABS(B8-A8)
Pulse la tecla Intro para obtener el resultado.
1,29 Calcular las horas laborables entre dos fechas excluyendo fines de semana
A veces, es posible que necesite contar las horas laborables entre dos fechas, excluyendo los fines de semana (sábados y domingos).
Aquí las horas laborables están establecidas en 8 horas diarias. Para calcular las horas laborables comprendidas entre las fechas indicadas en las celdas A16 y B16, utilice la siguiente fórmula:
=NETWORKDAYS(A16,B16) * 8
Pulse la tecla Intro y, a continuación, formatee el resultado como número o con formato general.
Para ver más ejemplos sobre el cálculo de horas laborables entre dos fechas, visite Obtener horas laborables entre dos fechas en Excel
Si tiene instalado Kutools para Excel en Excel, el 90 % de los cálculos de diferencias entre fechas y horas se resuelven rápidamente sin necesidad de recordar ninguna fórmula.
1,31 Calcular la diferencia entre dos fechas y horas mediante el Asistente de fecha y hora
Para calcular la diferencia entre dos fechas y horas en Excel, basta con usar el Asistente de Fecha y Hora.
1. Seleccione una celda donde desee colocar el resultado calculado y haga clic en Kutools > Asistente de fórmulas > Asistente de Fecha y Hora.
2. En el cuadro de diálogo emergente Asistente de Fecha y Hora, realice la siguiente configuración:
- Marque la opción Diferencia;
- Seleccione la fecha y hora de inicio y la fecha y hora de fin en la sección Entrada de argumento, también puede introducir manualmente la fecha y hora en el cuadro de entrada o hacer clic en el icono del calendario para seleccionar la fecha;
- Seleccione el Tipo de resultado de salida en el Lista desplegable;
- Vea una vista previa del resultado en la sección Resultado.
3. Haga clic en Aceptar. Se mostrará el resultado calculado; arrastre el controlador de autorrelleno al resto de las celdas que también necesite calcular.
Consejo:
Si desea obtener la diferencia entre dos fechas y horas y mostrar el resultado en días, horas y minutos con Kutools para Excel, proceda de la siguiente manera:
Seleccione una celda donde desee colocar el resultado y haga clic en Kutools > Asistente de fórmulas > Fecha y hora > Contar días, horas y minutos entre dos fechas.
A continuación, en el cuadro de diálogo Asistente de fórmulas, especifique la fecha de inicio y la fecha de fin, y luego haga clic en Aceptar.
Y el resultado de la diferencia se mostrará en días, horas y minutos.
Haga clic en Asistente de Fecha y Hora para descubrir más usos de esta función.
Haga clic en Kutools para Excel para descubrir todas las funciones de este complemento.
Haga clic en Descarga gratuitapara obtener una prueba gratuita de 30 días de Kutools para Excel
Si desea contar rápidamente los fines de semana, días laborables o un día de la semana específico entre dos fechas y horas, el grupo Asistente de fórmulas de Kutools para Excel puede ayudarle.
1. Seleccione la celda donde se mostrará el resultado calculado y haga clic en Kutools > Estadísticas > Número de días no laborables entre dos fechas / Número de días laborables entre dos fechas / Número de días de la semana entre dos fechas.
2. En el cuadro de diálogo emergente Asistente de fórmulas, especifique la fecha de inicio y la fecha de fin; si aplica Número de días de la semana entre dos fechas, también deberá indicar el día de la semana.
Para contar un día de la semana específico, consulte la nota sobre el uso de los números del 1 al 7 para indicar los días de domingo a sábado.
3. Haga clic en Aceptar y, a continuación, arrastre el controlador de autorrelleno sobre las celdas en las que necesite contar el número de fines de semana, días laborables o un día de la semana específico, si fuera necesario.
Haga clic en Kutools para Excelpara descubrir todas las funciones de este complemento.
Haga clic en Descarga gratuitapara obtener una prueba gratuita de 30 días de Kutools para Excel
2. Sumar o restar fechas y horas
Además de calcular la diferencia entre dos fechas y horas, sumar o restar es también una operación habitual con fechas y horas en Excel. Por ejemplo, podrías necesitar obtener la fecha de vencimiento a partir de la fecha de producción y el número de días de conservación de un producto.
2,11 Sumar o restar días a una fecha
Para sumar o restar un número determinado de días a una fecha, existen dos métodos distintos.
Suponiendo que desea sumar 21 días a una fecha en la celda A2, elija uno de los siguientes métodos para resolverlo:
Método 1 fecha+días
Seleccione una celda y escriba la fórmula:
=A+21
Pulse la tecla Intro para obtener el resultado.
Si desea restar 21 días, basta con cambiar el signo más (+) por el signo menos (-).
Método 2 Pegado Especial
1. Escriba el número de días que desea sumar en una celda, por ejemplo en la celda C2, y pulse Ctrl+C para copiarlo.
2. A continuación, seleccione las fechas a las que desea sumar 21 días, haga clic con el botón derecho para abrir el menú contextual y elija Pegado Especial....
3. En el cuadro de diálogo Pegado Especial, active la opción Sumar(si desea restar días, active la opción)Restar). Haga clic en Aceptar.
4. Ahora las fechas originales aparecen como números de cinco dígitos; aplíqueles formato de fecha.
2,12 Sumar o restar meses a una fecha
Para sumar o restar meses a una fecha, utilice la función FECHA.MES (EDATE).
Haga clic en FECHA.MES para estudiar sus argumentos y uso.
Suponiendo que desea sumar 6 meses a la fecha en la celda A2, utilice la siguiente fórmula:
=EDATE(A2,6)
Pulse la tecla Intro para obtener el resultado.
Si desea restar 6 meses a la fecha, sustituya 6 por -6.
2,13 Sumar o restar años a una fecha
Para sumar o restar n años a una fecha, puede usar una fórmula que combine las funciones FECHA, AÑO, MES y DÍA.
Suponiendo que desea sumar 3 años a la fecha en la celda A2, utilice la siguiente fórmula:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Pulse la tecla Intro para obtener el resultado.
Si desea restar 3 años a la fecha, sustituya 3 por -3.
2,14 Sumar o restar semanas a una fecha
Para sumar o restar semanas a una fecha, la fórmula general es
Suponiendo que desea sumar 4 semanas a la fecha en la celda A2, utilice la siguiente fórmula:
=A2+4*7
Pulse la tecla Intro para obtener el resultado.
Si desea restar 4 semanas a la fecha, sustituya el signo más (+) por el signo menos (-).
2,15 Sumar o restar días laborables incluyendo o excluyendo festivos
En esta sección se explica cómo usar la función DIA.LAB (WORKDAY) para sumar o restar días laborables a una fecha determinada, excluyendo o incluyendo festivos.
Visite DIA.LAB para conocer más detalles sobre sus argumentos y uso.
Sumar días laborables incluyendo festivos
En la celda A2 se encuentra la fecha que utiliza y en la celda B2 el número de días que desea sumar; utilice la siguiente fórmula:
=WORKDAY(A2,B2)
Pulse la tecla Intro para obtener el resultado.
Sumar días laborables excluyendo festivos
En la celda A5 se encuentra la fecha que utiliza, en la celda B5 el número de días que desea sumar y en el rango D5:D8 se enumeran los festivos; utilice la siguiente fórmula:
=WORKDAY(A5,B5,D5:D8)
Pulse la tecla Intro para obtener el resultado.
Nota:
La función DIA.LAB considera los sábados y domingos como fines de semana. Si sus fines de semana son sábado y domingo, puede usar la función DIA.LAB.INTL, que le permite especificar qué días constituyen el fin de semana.
Visite DIA.LAB.INTL para obtener más información.
Si desea restar días laborables a una fecha, basta con introducir un número negativo en la fórmula.
2,16 Sumar o restar año, mes y días específicos a una fecha
Si desea sumar un año, un mes y días específicos a una fecha, puede usar una fórmula que combine las funciones FECHA, AÑO, MES y DÍA.
Para sumar 1 año, 2 meses y 30 días a una fecha en A11, utilice la siguiente fórmula:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Pulse la tecla Intro para obtener el resultado.
Si desea restar, sustituya todos los signos más (+) por signos menos (-).
2,21 Sumar o restar horas/minutos/segundos a una fecha y hora
A continuación, te presentamos algunas fórmulas para sumar o restar horas, minutos o segundos a una fecha y hora.
Sumar o restar horas a una fecha y hora
Suponiendo que desea sumar 3 horas a una fecha y hora (también puede ser solo una hora) en la celda A2, utilice la siguiente fórmula:
=A2+3/24
Pulse la tecla Intro para obtener el resultado.
Sumar o restar minutos a una fecha y hora
Suponiendo que desea sumar 15 minutos a una fecha y hora (también puede ser solo una hora) en la celda A5, utilice la siguiente fórmula:
=A2+15/1440
Pulse la tecla Intro para obtener el resultado.
Sumar o restar segundos a una fecha y hora
Suponiendo que desea sumar 20 segundos a una fecha y hora (también puede ser solo una hora) en la celda A8, utilice la siguiente fórmula:
=A2+20/86400
Pulse la tecla Intro para obtener el resultado.
2,22 Sumar horas que superen las 24 horas
Supongamos que tienes una tabla de Excel que registra las horas trabajadas por todo el personal durante una semana. Para sumar el tiempo total con el fin de calcular los pagos, puedes usar SUM(rango) y obtener el resultado. Sin embargo, normalmente el total aparecerá como una hora que no supera las 24 horas, tal como se muestra en la siguiente captura de pantalla. ¿Cómo puedes obtener el resultado correcto?
En realidad, solo necesita dar formato al resultado como [hh]:mm:ss.
Haga clic con el botón derecho en la celda del resultado, elija Establecer formato de celda en el menú contextual y, en el cuadro de diálogo emergente Establecer formato de celda, seleccione Personalizada en la lista y escriba [hh]:mm:ss en el cuadro de texto de la sección derecha. Haga clic en Aceptar.
El resultado sumado se mostrará correctamente.
2,23 Sumar horas laborables a una fecha excluyendo fines de semana y festivos
Aquí se ofrece una fórmula avanzada para calcular la Fecha de fin sumando un número determinado de horas laborables a una Fecha de inicio, excluyendo fines de semana (sábados y domingos) y días festivos.
En una tabla de Excel, la celda A11 contiene la fecha y hora de inicio, y la celda B11, las horas laborables. Las celdas E11 y E13 indican la hora de inicio y la hora de fin de la jornada laboral, respectivamente, mientras que la celda E15 incluye el festivo que se excluirá.
Utilice la fórmula de la siguiente manera:
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
Pulse la tecla Intro para obtener el resultado.
Si tiene instalado Kutools para Excel, una sola herramienta —Asistente de fecha y hora— resuelve la mayoría de los cálculos de suma y resta de fechas y horas.
1. Haga clic en una celda donde quiera mostrar el resultado y aplique esta herramienta seleccionando Kutools > Asistente de fórmulas > Asistente de Fecha y Hora.
2. En el cuadro de diálogo Asistente de Fecha y Hora, active la opción Sumar o la opción Restar según sus necesidades. A continuación, elija la celda o escriba directamente la fecha y hora que desee utilizar en la sección Entrada de argumento, especifique los años, meses, semanas, días, horas, minutos y segundos que quiera sumar o restar y haga clic en Aceptar. Vea la captura de pantalla:
Puede previsualizar el resultado calculado en la sección Resultado.
Ahora se muestra el resultado. Arrastre el controlador de autorrelleno a otras celdas para obtener los resultados.
Haga clic en Asistente de Fecha y Horapara descubrir más usos de esta función.
Haga clic en Kutools para Excelpara descubrir todas las funciones de este complemento.
Haga clic en Descarga gratuitapara obtener una prueba gratuita de 30 días de Kutools para Excel
2,41 Marcar o resaltar si una fecha ha caducado
Si dispone de una lista de Fecha de Expiración de productos, es posible que desee marcar y resaltar las fechas que ya han caducado en relación con la fecha actual, tal como muestra la siguiente captura de pantalla.
En realidad, la función Usar formato condicional puede gestionar rápidamente esta tarea.
1. Seleccione las fechas que desea comprobar y, a continuación, haga clic en Inicio > Usar formato condicional > Regla nueva.
2. En el cuadro de diálogo Nueva regla de formato, seleccione Usar una fórmula que determine qué celdas se deben dar formato en la sección Seleccionar un tipo de regla y escriba =B2<HOY() en el cuadro de entrada (B2 es la primera fecha que desea comprobar). A continuación, haga clic en Formato para abrir el cuadro de diálogo Establecer formato de celda y elija un formato distintivo que resalte las fechas de expiración según sus necesidades. Haga clic en Aceptar > Aceptar.
Devolver el último día del mes actual o el primer día del Próximo mes
La fecha de expiración de algunos productos corresponde al último día del mes de producción o al primer día del mes siguiente. Para generar rápidamente la lista de fechas de expiración a partir de la fecha de producción, siga estos pasos.
Obtener el último día del mes actual
Aquí se muestra una fecha de producción en la celda B13. Utilice la siguiente fórmula:
=EOMONTH(B13,0)
Pulse la tecla Intro para obtener el resultado.
Obtener el primer día del Próximo mes
Aquí se muestra una fecha de producción en la celda B18. Utilice la siguiente fórmula:
=EOMONTH(B18,0)+1
Pulse la tecla Intro para obtener el resultado.
3. Calcula la edad
En esta sección se presentan los métodos para calcular la edad a partir de una fecha dada o de un número de serie.
3,11 Calcular la edad a partir de una fecha de nacimiento dada
Obtener la edad como número decimal a partir de la fecha de nacimiento
Haga clic en FRAC.AÑO para obtener detalles sobre sus argumentos y su uso.
Por ejemplo, para obtener las edades a partir de la lista de fechas de nacimiento en la columna B2:B9, utilice la siguiente fórmula:
=YEARFRAC(B2,TODAY())
Pulse la tecla Entrar y, a continuación, arrastre el controlador de autorrelleno hacia abajo hasta que se calculen todas las edades.
Consejo:
1) Puede especificar el número de decimales que desee en el cuadro de diálogo Establecer formato de celda.
2) Si desea calcular la edad en una fecha específica a partir de una fecha de nacimiento dada, sustituya HOY() por la fecha específica entre comillas dobles, como por ejemplo =YEARFRAC(B2,«[[PH_324]]»)
3) Si desea calcular la edad que tendrá el próximo año a partir de la fecha de nacimiento, simplemente añada 1 a la fórmula; por ejemplo: =FRAC.AÑO(B2;HOY())+1.
Obtener la edad como número entero a partir de la fecha de nacimiento
Haga clic en SIFECHA para obtener detalles sobre sus argumentos y su uso.
Siguiendo el ejemplo anterior, para obtener la edad a partir de las fechas de nacimiento de la lista en B2:B9, utilice la siguiente fórmula:
=DATEDIF(B2,TODAY(),«y»)
Pulse la tecla Entrar y, a continuación, arrastre el controlador de autorrelleno hacia abajo hasta que se calculen todas las edades.
Consejo:
1) Si desea calcular la edad en una fecha específica a partir de una fecha de nacimiento dada, sustituya HOY() por la fecha deseada entre comillas dobles, como en este ejemplo: =DATEDIF(B2,«[[PH_329]]»,«y»).
2) Si desea obtener la edad del próximo año a partir de la fecha de nacimiento, simplemente añada 1 a la fórmula, por ejemplo: =DATEDIF(B2,TODAY(),«y»)+1.
3,12 Calcular la edad en formato años, meses y días a partir de una fecha de nacimiento dada
Si desea calcular la edad a partir de una fecha de nacimiento dada y mostrar el resultado como «xx años, xx meses, xx días», tal como se muestra en la siguiente captura de pantalla, aquí tiene una fórmula larga que le ayudará.
Para obtener la edad en años, meses y días a partir de la fecha de nacimiento en la celda B12, utilice la siguiente fórmula:
=DATEDIF(B12,TODAY(),«Y»)&« Years, »&DATEDIF(B12,TODAY(),«YM»)&« Months, »&DATEDIF(B12,TODAY(),«MD»)&« Days»
Pulse la tecla Entrar para obtener la edad y, a continuación, arrastre el controlador de autorrelleno a otras celdas.
Consejo:
Si desea calcular la edad en una fecha específica a partir de una fecha de nacimiento dada, sustituya HOY() por la fecha deseada entre comillas dobles, como en este ejemplo: =DATEDIF(B12,«[[PH_337]]»,«Y»)&« años, »&SIFECHA(B12,«1/1/2021»,«YM»)&« meses, »&SIFECHA(B12,«1/1/2021»,«MD»)&« días».
3,13 Calcular la edad a partir de una fecha de nacimiento anterior a 1/1/1900
En Excel, no se puede introducir ni calcular correctamente ninguna fecha anterior a 1/1/1900. Sin embargo, si desea calcular la edad de una persona famosa a partir de su fecha de nacimiento (anterior a 1/11900) y su fecha de fallecimiento, solo un código VBA podrá ayudarle.
1. Pulse Alt+F11 para abrir la ventana de Microsoft Visual Basic para Aplicaciones, haga clic en la pestaña Insertar y seleccione Módulo para crear un nuevo módulo.
2. A continuación, copie y pegue el siguiente código en el nuevo módulo.
VBA: Calcular la edad antes de 1/1/1900
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. Guarde el código, vuelva a la hoja, seleccione una celda donde colocar la edad calculada y escriba =AgeFunc(fecha_nacimiento,fecha_fallecimiento). En este caso, =AgeFunc(B22,C22). Pulse Intro para obtener la edad y, si lo necesita, use el controlador de autorrelleno para aplicar esta fórmula a otras celdas.
Si tiene instalado Kutools para Excel en Excel, puede usar la herramienta Asistente de Fecha y Hora para calcular la edad.
1. Seleccione la celda donde desea insertar la edad calculada y haga clic en Kutools > Asistente de fórmulas > Asistente de Fecha y Hora.
2. En el cuadro de diálogo Asistente de Fecha y Hora,
- 1) Marque la opción Edad;
- 2) Elija la celda de fecha de nacimiento o introduzca directamente la fecha de nacimiento, o haga clic en el icono del calendario para seleccionarla;
- 3) Seleccione la opción Hoysi desea calcular la edad actual; seleccione la opción Fecha específicae introduzca la fecha si desea calcular la edad en el pasado o en el futuro;
- 4) Especifique el tipo de resultado en el Lista desplegable;
- 5) Vea una vista previa del resultado. Haga clic en Aceptar.
Haga clic en Asistente de Fecha y Horapara descubrir más usos de esta función.
Haga clic en Kutools para Excelpara descubrir todas las funciones de este complemento.
Haga clic en Descarga gratuitapara obtener una prueba gratuita de 30 días de Kutools para Excel
3,31 Obtener la fecha de nacimiento a partir del número de identificación
Si tiene una lista de números de identificación en la que los primeros 6 dígitos indican la fecha de nacimiento (por ejemplo, 920315330 corresponde al 15/03/1992), ¿cómo puede extraer rápidamente esa fecha en otra columna?
Ahora tomemos como ejemplo la lista de números de identificación que comienza en la celda C2 y utilicemos la siguiente fórmula:
=MID(C2,5,2)&«/»&MID(C2,3,2)&«/»&MID(C2,1,2)
Pulse la tecla Intro. A continuación, arrastre el controlador de autorrelleno hacia abajo para obtener el resto de resultados.
Nota:
En la fórmula, puede ajustar las referencias según sus necesidades. Por ejemplo, si el número de identificación es 13219920420392 y la fecha de nacimiento es 04/20/1992, puede modificarla a =EXTRAE(C2,8,2)&«/»&EXTRAE(C2,10,2)&«/»&EXTRAE(C2,4,4) para obtener el resultado correcto.
3,32 Calcular la edad a partir del número de identificación
Si dispone de una lista de números de identificación en los que los primeros 6 dígitos indican la fecha de nacimiento (por ejemplo, 920315330 corresponde al 15/03/1992), ¿cómo puede calcular rápidamente la edad a partir de cada número de identificación en Excel?
Ahora tomemos como ejemplo la lista de números de identificación que comienza en la celda C2 y utilicemos la siguiente fórmula:
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),«YY»),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),«y»)
Pulse la tecla Intro. Luego, arrastre el controlador de autorrelleno hacia abajo para obtener el resto de resultados.
Nota:
En esta fórmula, si el año es menor que el actual, se interpretará como si comenzara por «20»; por ejemplo, 200203943 se entenderá como el año 2020. Si el año es mayor que el actual, se considerará que empieza por «19»; por ejemplo, 920420392 se interpretará como el año 1992.
Más tutoriales de Excel:
Combinar varios libros de trabajo o hojas de cálculo en uno solo
Este tutorial cubre prácticamente todos los escenarios de combinación que puedas encontrar y te ofrece soluciones profesionales adaptadas a cada caso.
Dividir celdas de texto, número y fecha (separar en varias columnas)
Este tutorial se divide en tres partes: dividir celdas de texto, dividir celdas numéricas y dividir celdas de fecha. Cada una incluye ejemplos prácticos para que sepas exactamente cómo actuar cuando te enfrentes a estos casos.
Combinar el contenido de varias celdas sin perder datos en Excel
Este tutorial se centra en la extracción de texto o números desde una posición específica dentro de una celda y recopila distintos métodos para ayudarte a lograrlo en Excel.
Comparar dos columnas para encontrar coincidencias y diferencias en Excel
Este artículo cubre la mayoría de los escenarios que puede encontrarse al comparar dos columnas y espera ayudarle.
Las mejores herramientas de productividad para Office
Kutools para Excel resuelve la mayoría de sus problemas y aumenta su productividad en 80 %
- Super Barra de fórmulas (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (lea y edite cómodamente un gran número de celdas); Pegar en Rango de filtro...
- Fusionar celdas, filas o columnas manteniendo los datos; dividir el contenido de las celdas; combinar filas duplicadas y sumar o calcular el promedio...; evitar entradas duplicadas en celdas; comparar rangos...
- Seleccionar filas duplicadas o únicas;Seleccionar filas en blanco (todas las celdas vacías);Búsqueda avanzada y búsqueda difusa en varios libros de trabajo; selección aleatoria…
- Copia celdas exactamente sin que cambien las referencias de las fórmulas; crea referencias automáticamente en varias hojas; inserta viñetas, casillas de verificación y mucho más...
- Favoritas e inserción rápida de fórmulas, rangos, gráficos e imágenes; cifrado de celdas con contraseña; creación de listas de correo y envío de correos electrónicos…
- Extraer texto, agregar texto, eliminar caracteres en una posición, eliminar espacios; crear e imprimir estadísticas de la página de datos; convertir entre contenido de celdas y comentarios...
- Super Filtro (guarde y aplique esquemas de filtro en otras hojas); Clasificación avanzada por mes, semana, día, frecuencia y más; Filtro especial por negrita, cursiva…
- Combina libros y hojas de trabajo; combina tablas basadas en una columna clave; divide datos en varias hojas; conversión masiva de archivos XLS, XLSX y PDF...
- Agrupación en Tabla Dinámica por número de semana, día de la semana y más…Mostrar celdas desbloqueadas y bloquear la selección con colores diferenciados;Resaltar celdas que contienen fórmulas o nombres…
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y ahorre cientos de clics del ratón cada día!
