Tutorial de Excel: Cálculo de Fecha y Hora (calcular diferencia, edad, sumar/restar)
En Excel, el cálculo de fecha y hora se utiliza con frecuencia, como calcular la diferencia entre dos fechas/horas, sumar o restar fechas y horas, obtener la edad según la fecha de nacimiento dada, entre otros. En este tutorial, se enumeran casi todos los escenarios sobre el cálculo de fecha y hora y se proporcionan los métodos relacionados para ti.
En este tutorial, he creado algunos ejemplos para explicar los métodos; puedes cambiar las referencias según lo necesites cuando utilices el siguiente código VBA o fórmulas.
1. Calcular la diferencia entre dos fechas/horas
Calcular la diferencia entre dos fechas o dos horas puede ser el problema más habitual de cálculo de fecha y hora que encuentres en tu trabajo diario con Excel. Seguir los ejemplos proporcionados a continuación puede ayudarte a mejorar la eficiencia cuando te enfrentes a los mismos problemas.
1.11 Calcular la diferencia entre dos fechas en días/meses/años/semanas
La función DATEDIF de Excel se puede utilizar para calcular rápidamente la diferencia entre dos fechas en días, meses, años y semanas.
Haz clic para más detalles sobre la función DATEDIF
Diferencia de días entre dos fechas
Para obtener la diferencia de días entre dos fechas en las celdas A2 y B2, utiliza la siguiente fórmula:
=DATEDIF(A2,B2,"d")
Pulsa Enter para obtener el resultado.
Diferencia de meses entre dos fechas
Para obtener la diferencia de meses entre dos fechas en las celdas A5 y B5, utiliza la siguiente fórmula:
=DATEDIF(A5,B5,"m")
Pulsa Enter para obtener el resultado.
Diferencia de años entre dos fechas
Para obtener la diferencia de años entre dos fechas en las celdas A8 y B8, utiliza la siguiente fórmula:
=DATEDIF(A8,B8,"y")
Pulsa Enter para obtener el resultado.
Diferencia de semanas entre dos fechas
Para obtener la diferencia de semanas entre dos fechas en las celdas A11 y B11, utiliza la siguiente fórmula:
=DATEDIF(A11,B11,"d")/7
Pulsa Enter para obtener el resultado.
Nota:
1) Cuando utilices la fórmula anterior para obtener la diferencia de semanas, puede que el resultado se muestre en formato de fecha; debes cambiar el formato a general o número según lo necesites.
2) Cuando utilices la fórmula anterior para obtener la diferencia de semanas, puede que el resultado sea un número decimal. Si deseas obtener el número entero de semanas, puedes añadir la función ROUNDDOWN como se muestra a continuación para obtener la diferencia de semanas en número entero:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Calcular los meses ignorando años y días entre dos fechas
Si solo quieres calcular la diferencia de meses ignorando años y días entre dos fechas como se muestra en la siguiente captura de pantalla, aquí tienes una fórmula que puede ayudarte.
=DATEDIF(A2,B2,"ym")
Pulsa Enter para obtener el resultado.
A2 es la fecha de inicio y B2 es la fecha de fin.
1.13 Calcular los días ignorando años y meses entre dos fechas
Si solo quieres calcular la diferencia de días ignorando años y meses entre dos fechas como se muestra en la siguiente captura de pantalla, aquí tienes una fórmula que puede ayudarte.
=DATEDIF(A5,B5,"md")
Pulsa Enter 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 devolver años, meses y días
Si deseas obtener la diferencia entre dos fechas y mostrar xx años, xx meses y xx días como se muestra en la siguiente captura de pantalla, también se proporciona una fórmula.
=DATEDIF(A8, B8, "y") & " años, " & DATEDIF(A8, B8, "ym") & " meses, " & DATEDIF(A8, B8, "md") & " días"
Pulsa Enter 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 hoy, solo cambia la fecha de fin en las fórmulas anteriores por TODAY(). Aquí se muestra cómo calcular la diferencia de días entre una fecha pasada y hoy como ejemplo.
=DATEDIF(A11,TODAY(),"d")
Pulsa la tecla Enter para obtener el resultado.
Nota: si deseas calcular la diferencia entre una fecha futura y hoy, cambia la fecha de inicio por hoy y toma la fecha futura como fecha de fin de esta manera:
=DATEDIF(TODAY(),A14,"d")
Ten en cuenta que la fecha de inicio debe ser menor que la fecha de fin en la función DATEDIF; de lo contrario, devolverá el valor de error #NÚM!.
1.16 Calcular los días laborables con o sin festivos entre dos fechas
A veces, es posible que necesites contar el número de días laborables con o sin los festivos entre dos fechas dadas.
En esta parte, utilizarás la función NETWORKDAYS.INTL:
Haz clic en NETWORKDAYS.INTL para conocer sus argumentos y uso.
Contar días laborables con festivos
Para contar los días laborables con festivos entre dos fechas en las celdas A2 y B2, utiliza la siguiente fórmula:
=NETWORKDAYS.INTL(A2,B2)
Pulsa Enter para obtener el resultado.
Contar días laborables sin festivos
Para contar los días laborables con festivos entre dos fechas en las celdas A2 y B2, y excluyendo los festivos en el rango D5:D9, utiliza la siguiente fórmula:
=NETWORKDAYS.INTL(A5,B5,1,D5:D9)
Pulsa Enter para obtener el resultado.
Nota:
En las fórmulas anteriores, se consideran sábado y domingo como fin de semana. Si tienes otros días de fin de semana, cambia el argumento [weekend] según lo necesites.
1.17 Calcular los fines de semana entre dos fechas
Si deseas contar el número de fines de semana entre dos fechas, las funciones SUMPRODUCT o SUM pueden ayudarte.
Para contar los fines de semana (sábado y domingo) entre dos fechas en las celdas A12 y B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
O
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Pulsa Enter para obtener el resultado.
1.18 Calcular un día de la semana específico entre dos fechas
Para contar el número de un día de la semana específico, como lunes, entre dos fechas, la combinación de las funciones INT y WEEKDAY puede ayudarte.
Las celdas A15 y B15 son las dos fechas entre las que deseas contar los lunes; utiliza la siguiente fórmula:
=INT((WEEKDAY(A15-2)-A15 +B15)/7)
Pulsa Enter para obtener el resultado.
Cambia el número del día de la semana en la función WEEKDAY 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 y7 es sábado)
1.19 Calcular los días restantes en el mes/año
A veces, es posible que quieras saber los días restantes en el mes o año según la fecha proporcionada, como se muestra en la siguiente captura de pantalla:
Obtener los días restantes en el mes actual
Haz clic en EOMONTH para conocer el argumento y uso.
Para obtener los días restantes del mes actual en la celda A2, utiliza la siguiente fórmula:
=EOMONTH(A2,0)-A2
Pulsa Enter y arrastra el controlador de relleno automático para aplicar esta fórmula a otras celdas si es necesario.
Consejo: los resultados pueden mostrarse en formato de fecha; simplemente cámbialos a formato general o número.
Obtener los días restantes en el año actual
Para obtener los días restantes del año actual en la celda A2, utiliza la siguiente fórmula:
=DATE(YEAR(A2),12,31)-A2
Pulsa Enter y arrastra el controlador de relleno automático para aplicar esta fórmula a otras celdas si es necesario.
1.21 Calcular la diferencia entre dos horas
Para obtener la diferencia entre dos horas, aquí tienes dos fórmulas sencillas que pueden ayudarte.
Suponiendo que en las celdas A2 y B2 se encuentran la hora de inicio y la hora de fin respectivamente, utiliza las siguientes fórmulas:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Pulsa Enter para obtener el resultado.
Nota:
- Si usas end_time-start_time, puedes cambiar el formato del resultado a otro formato de hora en el cuadro de diálogo Formato de celdas.
- Si usas TEXT(end_time-first_time,"time_format"), introduce el formato de hora que deseas mostrar en la fórmula, por ejemplo, TEXT(end_time-first_time,"h") devuelve16.
- Si la hora de fin es menor que la hora de inicio, ambas fórmulas devolverán valores de error. Para solucionar este problema, puedes añadir ABS delante de estas fórmulas, como ABS(B2-A2), ABS(TEXT(B2-A2,"hh:mm:ss")), y luego formatea el resultado como hora.
1.22 Calcular la diferencia entre dos horas en horas/minutos/segundos
Si deseas calcular la diferencia entre dos horas en horas, minutos o segundos como se muestra en la siguiente captura de pantalla, sigue esta parte.
Obtener la diferencia en horas entre dos horas
Para obtener la diferencia en horas entre dos horas en A5 y B5, utiliza la siguiente fórmula:
=INT((B5-A5)*24)
Pulsa Enter y luego formatea el resultado como general o número.
Si deseas obtener la diferencia en horas decimales, usa (end_time-start_time)*24.
Obtener la diferencia en minutos entre dos horas
Para obtener la diferencia en minutos entre dos horas en A8 y B8, utiliza la siguiente fórmula:
=INT((B8-A8)*1440)
Pulsa Enter y luego formatea el resultado como general o número.
Si deseas obtener la diferencia en minutos decimales, usa (end_time-start_time)*1440.
Obtener la diferencia en segundos entre dos horas
Para obtener la diferencia en segundos entre dos horas en A5 y B5, utiliza la siguiente fórmula:
=(B11-A11)*86400)
Pulsa Enter y luego formatea el resultado como general o número.
1.23 Calcular solo la diferencia en horas entre dos horas (sin exceder24 horas)
Si la diferencia entre dos horas no supera las24 horas, la función HOUR puede obtener rápidamente la diferencia en horas entre esas dos horas.
Haz clic en HOUR para más detalles sobre esta función.
Para obtener la diferencia en horas entre las horas en las celdas A14 y B14, utiliza la función HOUR de esta manera:
=HOUR(B14-A14)
Pulsa Enter para obtener el resultado.
La hora de inicio debe ser menor que la hora de fin; de lo contrario, la fórmula devuelve el valor de error #NÚM!.
1.24 Calcular solo la diferencia en minutos entre dos horas (sin exceder60 minutos)
La función MINUTE puede obtener rápidamente solo la diferencia en minutos entre esas dos horas e ignorar horas y segundos.
Haz clic en MINUTE para más detalles sobre esta función.
Para obtener solo la diferencia en minutos entre las horas en las celdas A17 y B17, utiliza la función MINUTE de esta manera:
=MINUTE(B17-A17)
Pulsa Enter para obtener el resultado.
La hora de inicio debe ser menor que la hora de fin; de lo contrario, la fórmula devuelve el valor de error #NÚM!.
1.25 Calcular solo la diferencia en segundos entre dos horas (sin exceder60 segundos)
La función SECOND puede obtener rápidamente solo la diferencia en segundos entre esas dos horas e ignorar horas y minutos.
Haz clic en SECOND para más detalles sobre esta función.
Para obtener solo la diferencia en segundos entre las horas en las celdas A20 y B20, utiliza la función SECOND de esta manera:
=SECOND(B20-A20)
Pulsa Enter para obtener el resultado.
La hora de inicio debe ser menor que la hora de fin; de lo contrario, la fórmula devuelve el valor de error #NÚM!.
1.26 Calcular la diferencia entre dos horas y devolver horas, minutos y segundos
Si deseas mostrar la diferencia entre dos horas como xx horas xx minutos xx segundos, utiliza la función TEXT como se muestra a continuación:
Haz clic en TEXT para conocer los argumentos y el uso de esta función.
Para calcular la diferencia entre las horas en las celdas A23 y B23, utiliza la siguiente fórmula:
=TEXT(B23-A23,"h"" horas ""m"" minutos ""s"" segundos"").
Pulsa Enter para obtener el resultado.
Nota:
Esta fórmula solo calcula la diferencia en horas que no exceda las24 horas, y la hora de fin debe ser mayor que la de inicio; de lo contrario, devuelve el valor de error #VALOR!.
1.27 Calcular la diferencia entre dos fechas y horas
Si tienes dos horas en formato mm/dd/yyyy hh:mm:ss, para calcular la diferencia entre ellas, puedes usar una de las siguientes fórmulas según lo necesites.
Obtener la diferencia de tiempo entre dos fechas y horas y devolver el resultado en formato hh:mm:ss
Tomando como ejemplo dos fechas y horas en las celdas A2 y B2, utiliza la siguiente fórmula:
=B2-A2
Pulsa Enter para obtener un resultado en formato de fecha y hora; luego, formatea este resultado como [h]:mm:ss en la categoría personalizada bajo la Número pestaña en Formato de celdas diálogo.
Obtener la diferencia entre dos fechas y horas y devolver días, horas, minutos y segundos
Tomando como ejemplo dos fechas y horas en las celdas A5 y B5, utiliza la siguiente fórmula:
=INT(B5-A5) & " Días, " & HOUR(B5-A5) & " Horas, " & MINUTE(B5-A5) & " Minutos, " & SECOND(B5-A5) & " Segundos "
Pulsa Enter para obtener el resultado.
Nota: en ambas fórmulas, la fecha y hora de fin debe ser mayor que la de inicio; de lo contrario, las fórmulas devolverán valores de error.
1.28 Calcular la diferencia de tiempo con milisegundos
Primero, necesitas saber cómo formatear la celda para mostrar los milisegundos:
Selecciona las celdas en las que deseas mostrar milisegundos y haz clic derecho para seleccionar Formato de celdas para habilitar el Formato de celdas diálogo, selecciona Personalizado en la Categoría lista bajo la pestaña Número, y escribe esto hh:mm:ss.000 en el cuadro de texto.
Usa la fórmula:
Aquí, para calcular la diferencia entre dos horas en las celdas A8 y B8, utiliza la fórmula:
=ABS(B8-A8)
Pulsa Enter para obtener el resultado.
1.29 Calcular las horas laborables entre dos fechas excluyendo fines de semana
A veces, es posible que necesites contar las horas laborables entre dos fechas, excluyendo los fines de semana (sábado y domingo).
Aquí las horas laborables están fijadas en8 horas cada día, y para calcular las horas laborables entre dos fechas proporcionadas en las celdas A16 y B16, utiliza la siguiente fórmula:
=NETWORKDAYS(A16,B16) *8
Pulsa Enter y luego formatea el resultado como general o número.
Para más ejemplos sobre cómo calcular las horas laborables entre dos fechas, visita Obtener horas de trabajo entre dos fechas en Excel
Si tienes Kutools para Excel instalado en Excel, el90% de los cálculos de diferencia de fecha y hora se pueden resolver rápidamente sin recordar ninguna fórmula.
1.31 Calcular la diferencia entre dos fechas y horas con el Asistente de Fecha y Hora
Para calcular la diferencia entre dos fechas y horas en Excel, solo necesitas el Asistente de Fecha y Hora es suficiente.
1. Selecciona una celda donde colocar el resultado calculado y haz 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, sigue las siguientes opciones:
- Marca la opción Diferencia;
- Selecciona la fecha y hora de inicio y fin en la sección Entrada de argumento; también puedes introducir la fecha y hora manualmente en el cuadro de entrada, o hacer clic en el icono de calendario para seleccionar la fecha;
- Selecciona el tipo de resultado de salida en la lista desplegable;
- Previsualiza el resultado en la sección Resultado.
3. Haz clic en Ok. El resultado calculado se muestra y arrastra el controlador de relleno automático sobre las celdas que también necesites calcular.
Consejo:
Si deseas obtener la diferencia entre dos fechas y horas y mostrar el resultado como días, horas y minutos con Kutools para Excel, haz lo siguiente:
Selecciona una celda donde quieras colocar el resultado y haz clic en Kutools > Asistente de fórmulas > Fecha y hora > Contar días, horas y minutos entre dos fechas.
Luego, en el Asistente de fórmulas diálogo, especifica la fecha de inicio y fin, luego haz clic en Ok.
Y el resultado de la diferencia se mostrará como días, horas y minutos.
Haz clic en Asistente de Fecha y Hora para conocer más usos de esta función.
Haz clic en Kutools para Excel para conocer todas las funciones de este complemento.
Haz clic en Descarga gratuita para obtener 30 días de prueba gratuita de Kutools para Excel
Si deseas 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 ayudarte. Asistente de fórmulas puede ayudarte.
1. Selecciona la celda donde colocarás el resultado calculado, haz clic en Kutools > Contar > 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, especifica la fecha de inicio y fin; si aplicas Número de días de la semana entre dos fechas, también debes especificar el día de la semana.
Para contar el día de la semana específico, puedes consultar la nota para usar1-7 para indicar de domingo a sábado.
3. Haz clic en Ok y luego arrastra el controlador de relleno automático sobre las celdas que necesiten contar el número de fines de semana/días laborables/día de la semana específico si es necesario.
Haz clic en Kutools para Excel para conocer todas las funciones de este complemento.
Haz clic en Descarga gratuita para obtener 30 días de prueba gratuita de Kutools para Excel
2. Sumar o restar fecha y hora
Además de calcular la diferencia entre dos fechas y horas, sumar o restar también es un cálculo habitual en Excel. Por ejemplo, puedes querer obtener la fecha de vencimiento según 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 específico de días a una fecha, aquí tienes dos métodos diferentes.
Suponiendo que deseas sumar21 días a una fecha en la celda A2, elige uno de los siguientes métodos para resolverlo:
Método1 fecha+días
Selecciona una celda y escribe la fórmula:
=A+21
Pulsa Enter para obtener el resultado.
Si deseas restar21 días, solo cambia el signo más (+) por el signo menos (-).
Método2 Pegado Especial
1. Escribe el número de días que deseas sumar en una celda, por ejemplo en la celda C2, y luego pulsa Ctrl + C para copiarlo.
2. Luego selecciona las fechas a las que deseas sumar21 días, haz clic derecho para mostrar el menú contextual y selecciona Pegado especial....
3. En el Pegado especial diálogo, marca la opción Agregar (Si deseas restar días, marca la opción Restar ). Haz clic en OK.
4. Ahora las fechas originales cambian a números de5 dígitos; formatea esas celdas como fechas.
2.12 Sumar o restar meses a una fecha
Para sumar o restar meses a una fecha, se puede usar la función EDATE.
Haz clic en EDATE para estudiar sus argumentos y uso.
Suponiendo que deseas sumar6 meses a la fecha en la celda A2, utiliza la siguiente fórmula:
=EDATE(A2,6)
Pulsa Enter para obtener el resultado.
Si deseas restar6 meses a la fecha, cambia6 por -6.
2.13 Sumar o restar años a una fecha
Para sumar o restar n años a una fecha, se puede usar una fórmula que combine las funciones DATE, YEAR, MONTH y DAY.
Suponiendo que deseas sumar3 años a la fecha en la celda A2, utiliza la siguiente fórmula:
=DATE(YEAR(A2) +3, MONTH(A2),DAY(A2))
Pulsa Enter para obtener el resultado.
Si deseas restar3 años a la fecha, cambia3 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 deseas sumar4 semanas a la fecha en la celda A2, utiliza la siguiente fórmula:
=A2+4*7
Pulsa Enter para obtener el resultado.
Si deseas restar4 semanas a la fecha, cambia 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 WORKDAY para sumar o restar días laborables a una fecha dada excluyendo o incluyendo festivos.
Visita WORKDAY para conocer más detalles sobre sus argumentos y uso.
Sumar días laborables incluyendo festivos
En la celda A2 está la fecha que usas, en la celda B2 el número de días que deseas sumar; utiliza la siguiente fórmula:
=WORKDAY(A2,B2)
Pulsa Enter para obtener el resultado.
Sumar días laborables excluyendo festivos
En la celda A5 está la fecha que usas, en la celda B5 el número de días que deseas sumar, en el rango D5:D8 se listan los festivos; utiliza la siguiente fórmula:
=WORKDAY(A5,B5,D5:D8)
Pulsa Enter para obtener el resultado.
Nota:
La función WORKDAY considera sábado y domingo como fines de semana; si tus fines de semana son sábado y domingo, puedes aplicar la función WORKDAY.INTL, que permite especificar los fines de semana.
Visita WORKDAY.INTL para más detalles.
Si deseas restar días laborables a una fecha, solo cambia el número de días a negativo en la fórmula.
2.16 Sumar o restar año, mes y días específicos a una fecha
Si deseas sumar un año, mes y días específicos a una fecha, la fórmula que combina las funciones DATE, YEAR, MONTH y DAYS puede ayudarte.
Para sumar1 año,2 meses y30 días a una fecha en A11, utiliza la siguiente fórmula:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Pulsa Enter para obtener el resultado.
Si deseas restar, cambia todos los signos más (+) por signos menos (-).
2.21 Sumar o restar horas/minutos/segundos a una fecha y hora
Aquí se proporcionan 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 deseas sumar3 horas a una fecha y hora (también puede ser una hora) en la celda A2, utiliza la siguiente fórmula:
=A2+3/24
Pulsa Enter para obtener el resultado.
Sumar o restar minutos a una fecha y hora
Suponiendo que deseas sumar15 minutos a una fecha y hora (también puede ser una hora) en la celda A5, utiliza la siguiente fórmula:
=A2+15/1440
Pulsa Enter para obtener el resultado.
Sumar o restar segundos a una fecha y hora
Suponiendo que deseas sumar20 segundos a una fecha y hora (también puede ser una hora) en la celda A8, utiliza la siguiente fórmula:
=A2+20/86400
Pulsa Enter para obtener el resultado.
2.22 Sumar horas que superan las24 horas
Suponiendo que hay una tabla de Excel que registra el tiempo de trabajo de todo el personal en una semana, para sumar el tiempo total de trabajo y calcular los pagos, puedes usar SUM(rango) para obtener el resultado. Pero, en general, el resultado sumado se mostrará como una hora que no supera las24 horas, como se muestra en la siguiente captura de pantalla. ¿Cómo puedes obtener el resultado correcto?
En realidad, solo necesitas formatear el resultado como [hh]:mm:ss.
Haz clic derecho en la celda del resultado, elige Formato de celdas en el menú contextual, y en el cuadro de diálogo emergente Formato de celdas elige Personalizado de la lista, y escribe [hh]:mm:ss en el cuadro de texto de la derecha, haz clic en OK.
El resultado sumado se mostrará correctamente.
2.23 Sumar horas laborables a una fecha excluyendo fines de semana y festivos
Aquí se proporciona una fórmula larga para obtener la fecha de finalización sumando un número específico de horas laborables a una fecha de inicio y excluyendo fines de semana (sábado y domingo) y festivos.
En una tabla de Excel, A11 contiene la fecha y hora de inicio, B11 contiene las horas laborables, en las celdas E11 y E13 están las horas de inicio y fin de la jornada laboral, y la celda E15 contiene el festivo que se excluirá.
Utiliza la siguiente fórmula:
=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))
Pulsa Enter para obtener el resultado.
Si tienes Kutools para Excel instalado, solo una herramienta – Asistente de Fecha y Horapuede resolver la mayoría de los cálculos de suma y resta de fecha y hora.
1. Haz clic en una celda donde quieras mostrar el resultado y aplica esta herramienta haciendo clic en Kutools > Asistente de fórmulas > Asistente de Fecha y Hora.
2. En el cuadro de diálogo Asistente de Fecha y Hora, marca la opción Agregar o Restar según lo necesites, luego elige la celda o escribe directamente la fecha y hora que deseas usar en la sección Entrada de argumento, luego especifica los años, meses, semanas, días, horas, minutos y segundos que deseas sumar o restar, y haz clic en Ok. Ver captura de pantalla:
Puedes previsualizar el resultado calculado en la Resultado sección.
Ahora el resultado se muestra; arrastra el controlador de relleno automático sobre otras celdas para obtener los resultados.
Haz clic en Asistente de Fecha y Hora para conocer más usos de esta función.
Haz clic en Kutools para Excel para conocer todas las funciones de este complemento.
Haz clic en Descarga gratuita para obtener 30 días de prueba gratuita de Kutools para Excel
2.41 Comprobar o resaltar si una fecha está caducada
Si tienes una lista de fechas de caducidad de productos, es posible que desees comprobar y resaltar las fechas que ya han caducado en función de hoy, como se muestra en la siguiente captura de pantalla.
En realidad, el Formato condicional puede gestionar rápidamente esta tarea.
1. Selecciona las fechas que deseas comprobar y haz clic en Inicio > Formato condicional > Nueva regla.
2. En el Nueva regla de formato diálogo, selecciona Usar una fórmula para determinar qué celdas formatear en la Seleccionar un tipo de regla sección, y escribe =B2
2.42 Devolver el final del mes actual/primer día del próximo mes
Las fechas de caducidad de algunos productos son al final del mes de producción o el primer día del mes siguiente; para listar rápidamente las fechas de caducidad según la fecha de producción, sigue esta parte.
Obtener el final del mes actual
Aquí hay una fecha de producción en la celda B13; utiliza la siguiente fórmula:
=EOMONTH(B13,0)
Pulsa Enter para obtener el resultado.
Obtener el primer día del próximo mes
Aquí hay una fecha de producción en la celda B18; utiliza la siguiente fórmula:
=EOMONTH(B18,0)+1
Pulsa Enter para obtener el resultado.
3. Calcular la edad
En esta sección se enumeran los métodos para resolver cómo calcular la edad en función de una fecha dada o un número de serie.
3.11 Calcular la edad según la fecha de nacimiento dada
Obtener la edad en número decimal según la fecha de nacimiento
Haz clic en YEARFRAC para ver detalles sobre sus argumentos y uso.
Por ejemplo, para obtener las edades según la lista de fechas de nacimiento en la columna B2:B9, utiliza la siguiente fórmula:
=YEARFRAC(B2,TODAY())
Pulsa Enter luego arrastra el controlador de relleno automático hacia abajo hasta que todas las edades estén calculadas.
Consejo:
1) Puedes especificar los decimales que necesites en el Formato de celdas diálogo.
2) Si deseas calcular la edad en una fecha específica según una fecha de nacimiento dada, cambia TODAY() por la fecha específica entre comillas dobles, por ejemplo: =YEARFRAC(B2,"1/1/2021")
3) Si deseas obtener la edad del próximo año según la fecha de nacimiento, solo suma1 en la fórmula, por ejemplo: =YEARFRAC(B2,TODAY())+1.
Obtener la edad en número entero según la fecha de nacimiento
Haz clic en DATEDIF para ver detalles sobre sus argumentos y uso.
Usando el ejemplo anterior, para obtener la edad según las fechas de nacimiento en la lista B2:B9, utiliza la siguiente fórmula:
=DATEDIF(B2,TODAY(),"y")
Pulsa Enter luego arrastra el controlador de relleno automático hacia abajo hasta que todas las edades estén calculadas.
Consejo:
1) Si deseas calcular la edad en una fecha específica según una fecha de nacimiento dada, cambia TODAY() por la fecha específica entre comillas dobles, por ejemplo: =DATEDIF(B2,"1/1/2021","y").
2) Si deseas obtener la edad del próximo año según la fecha de nacimiento, solo suma1 en la fórmula, por ejemplo: =DATEDIF(B2,TODAY(),"y")+1.
3.12 Calcular la edad en formato años, meses y días según la fecha de nacimiento dada
Si deseas calcular la edad según una fecha de nacimiento dada y mostrar el resultado como xx años, xx meses, xx días como se muestra en la siguiente captura de pantalla, aquí tienes una fórmula larga que puede ayudarte.
Para obtener la edad en años, meses y días según la fecha de nacimiento en la celda B12, utiliza la siguiente fórmula:
=DATEDIF(B12,TODAY(),"Y")&" Años, "&DATEDIF(B12,TODAY(),"YM")&" Meses, "&DATEDIF(B12,TODAY(),"MD")&" Días"
Pulsa Enter para obtener la edad, luego arrastra el controlador de relleno automático hacia abajo a otras celdas.
Consejo:
Si deseas calcular la edad en una fecha específica según una fecha de nacimiento dada, cambia TODAY() por la fecha específica entre comillas dobles, por ejemplo: =DATEDIF(B12,"1/1/2021","Y")&" Años, "&DATEDIF(B12,"1/1/2021","YM")&" Meses, "&DATEDIF(B12,"1/1/2021","MD")&" Días".
3.13 Calcular la edad por fecha de nacimiento anterior al1/1/1900
En Excel, no se puede introducir ni calcular correctamente una fecha anterior al1/1/1900. Pero si deseas calcular la edad de una persona famosa según la fecha de nacimiento (anterior al1/1/1900) y la fecha de fallecimiento, solo un código VBA puede ayudarte.
1. Pulsa las teclas Alt + F11 para abrir la ventana Microsoft Visual Basic para Aplicaciones, haz clic en la pestaña Insertar y elige Módulo para crear un nuevo módulo.
2. Luego copia y pega el siguiente código en el nuevo módulo.
VBA: Calcular edad antes del1/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. Guarda el código, vuelve a la hoja y selecciona una celda para colocar la edad calculada, escribe =AgeFunc(birthdate,deathdate), en este caso, =AgeFunc(B22,C22), pulsa la tecla Enter para obtener la edad. Y usa el controlador de relleno automático para aplicar esta fórmula a otras celdas si es necesario.
Si tienes Kutools for Excel instalado en Excel, puedes aplicar la herramienta Asistente de Fecha y Hora para calcular la edad.
1. Selecciona una celda donde quieras colocar la edad calculada y haz 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) Marca la opción Edad;
- 2) Elige la celda de la fecha de nacimiento o introduce directamente la fecha de nacimiento o haz clic en el icono de calendario para seleccionarla;
- 3) Elige la opción Hoy si deseas calcular la edad actual, elige la opción Fecha específica e introduce la fecha si deseas calcular la edad en el pasado o futuro;
- 4) Especifica el tipo de salida en la lista desplegable;
- 5) Previsualiza el resultado de salida. Haz clic en Ok.
Haz clic en Asistente de Fecha y Hora para conocer más usos de esta función.
Haz clic en Kutools para Excel para conocer todas las funciones de este complemento.
Haz clic en Descarga gratuita para obtener 30 días de prueba gratuita de Kutools para Excel
3.31 Obtener la fecha de nacimiento a partir del número de identificación
Si tienes una lista de números de identificación que usan los primeros6 dígitos para registrar la fecha de nacimiento, por ejemplo,920315330 significa que la fecha de nacimiento es15/03/1992, ¿cómo puedes obtener rápidamente la fecha de nacimiento en otra columna?
Ahora, toma como ejemplo la lista de números de identificación que comienza en la celda C2 y utiliza la siguiente fórmula:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Pulsa Enter tecla. Luego arrastra el controlador de relleno automático hacia abajo para obtener otros resultados.
Nota:
En la fórmula, puedes cambiar la referencia según lo necesites. Por ejemplo, si el número de identificación se muestra como13219920420392, la fecha de nacimiento es20/04/1992, puedes cambiar la fórmula a =MID(C2,8,2)&"/"&MID(C2,10,2)&"/"&MID(C2,4,4) para obtener el resultado correcto.
3.32 Calcular la edad a partir del número de identificación
Si tienes una lista de números de identificación que usan los primeros6 dígitos para registrar la fecha de nacimiento, por ejemplo,920315330 significa que la fecha de nacimiento es15/03/1992, ¿cómo puedes calcular rápidamente la edad según cada número de identificación en Excel?
Ahora, toma como ejemplo la lista de números de identificación que comienza en la celda C2 y utiliza 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")
Pulsa Enter tecla. Luego arrastra el controlador de relleno automático hacia abajo para obtener otros resultados.
Nota:
En esta fórmula, si el año es menor que el año actual, se considerará que el año comienza con20, por ejemplo,200203943 se considerará como año2020; si el año es mayor que el año actual, se considerará que el año comienza con19, por ejemplo,920420392 se considerará como año1992.
Más tutoriales de Excel:
Combinar varios libros de trabajo/hojas de cálculo en uno solo
Este tutorial enumera casi todos los escenarios de combinación que puedes encontrar y proporciona soluciones profesionales relativas para ti.
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 parte proporciona diferentes ejemplos para ayudarte a saber cómo manejar la tarea de división cuando te enfrentes al mismo problema.
Combinar el contenido de varias celdas sin perder datos en Excel
Este tutorial reduce la extracción a una posición específica en una celda y recopila diferentes métodos para ayudar a extraer texto o números de una celda por posición específica en Excel.
Comparar dos columnas para coincidencias y diferencias en Excel
Este artículo cubre la mayoría de los escenarios posibles al comparar dos columnas que podrías encontrar, y espera poder ayudarte.
Las Mejores Herramientas de Productividad para Office
Kutools para Excel resuelve la mayoría de tus problemas y aumenta tu productividad en un 80%
- Barra de Fórmulas Super (editar fácilmente múltiples líneas de texto y fórmulas); Diseño de Lectura (leer y editar fácilmente grandes cantidades de celdas); Pegar en Rango Filtrado...
- Combinar Celdas/Filas/Columnas manteniendo los datos; Dividir contenido de celdas; Combinar filas duplicadas y Sumar/Promediar... Prevenir celdas duplicadas; Comparar rangos...
- Seleccionar filas duplicadas o únicas; Seleccionar filas en blanco (todas las celdas están vacías); Búsqueda Avanzada y Búsqueda Difusa en muchos libros de trabajo; Selección Aleatoria...
- Copia Exacta de múltiples celdas sin cambiar la referencia de la fórmula; Crear automáticamente referencias a múltiples hojas; Insertar viñetas, casillas de verificación y más...
- Favoritos e inserción rápida de fórmulas, rangos, gráficos e imágenes; Encriptar celdas con contraseña; Crear lista de correo y enviar correos electrónicos...
- Extraer texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir estadísticas de página; Convertir entre el contenido de las celdas y los comentarios...
- Super Filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes/semana/día, frecuencia y más; Filtro especial por negrita, cursiva...
- Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión masiva de xls, xlsx y PDF...
- Agrupación de Tabla Dinámica por número de semana, día de la semana y más... Mostrar celdas desbloqueadas, bloqueadas por diferentes colores; Resaltar celdas que tienen fórmula/nombre...
- Habilitar edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abrir y crear varios documentos en nuevas pestañas de la misma ventana, en lugar de en ventanas nuevas.
- ¡Aumenta tu productividad en un 50%, y reduce cientos de clics del ratón cada día!
