Tutorial de Excel: Cálculo de fecha y hora (calcular diferencia, edad, sumar/restar)
En Excel, el cálculo de fecha y hora se usa con frecuencia, como calcular la diferencia entre dos fechas/horas, sumar o restar fecha y hora, obtener la edad en función de la fecha de nacimiento, etc. Aquí, en este tutorial, enumera casi escenarios sobre el cálculo de fecha y hora y proporciona los métodos relacionados para usted.
En este tutorial, creo algunos ejemplos para explicar los métodos, puede cambiar las referencias que necesita cuando usa el código o las fórmulas de VBA a continuación.
1. Calcular la diferencia entre dos fechas/horas
Calcular la diferencia entre dos fechas o dos horas puede ser el problema más normal de cálculo de fecha y hora que encuentre en el trabajo diario de Excel. Los siguientes ejemplos proporcionados a continuación pueden ayudarlo a mejorar la eficiencia cuando encuentre los mismos problemas.
1.11 Calcular la diferencia entre dos fechas en días/meses/años/semanas
La función SIFECHA de Excel se puede utilizar para calcular rápidamente la diferencia entre dos fechas en días, meses, años y semanas.
Haga clic para obtener más detalles sobre SIFECHA función
Días de diferencia entre dos fechas
Para obtener la diferencia de días entre dos fechas en la celda A2 y B2, use la fórmula como esta
=DATEDIF(A2,B2,"d")
Prensa Participar clave para obtener el resultado.
Meses de diferencia entre dos fechas
Para obtener la diferencia de meses entre dos fechas en las celdas A5 y B5, use la fórmula como esta
=DATEDIF(A5,B5,"m")
Prensa Participar clave para obtener el resultado.
Años de diferencia entre dos fechas
Para obtener la diferencia de años entre dos fechas en las celdas A8 y B8, use la fórmula como esta
=DATEDIF(A8,B8,"y")
Prensa Participar clave para obtener el resultado.
Semanas de diferencia entre dos fechas
Para obtener la diferencia de semanas entre dos fechas en las celdas A11 y B11, use la fórmula como esta
=DATEDIF(A11,B11,"d")/7
Prensa Participar clave para obtener el resultado.
Nota:
1) Cuando usa la fórmula anterior para obtener la diferencia de semanas, puede devolver un resultado en formato de fecha, debe formatear el resultado en general o número según lo necesite.
2) Cuando usa la fórmula anterior para obtener la diferencia de semanas, puede volver a un número decimal, si desea obtener el número de semana entero, puede agregar la función ROUNDDOWN antes como se muestra a continuación para obtener la diferencia de semanas enteras:
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 Calcular meses ignorar años y días entre dos fechas
Si solo desea calcular la diferencia de meses ignorando los años y los días entre dos fechas como muestra la siguiente captura de pantalla, aquí hay una fórmula que puede ayudarlo.
=DATEDIF(A2,B2,"ym")
Prensa Participar clave para obtener el resultado.
A2 es la fecha de inicio y B2 es la fecha de finalización.
1.13 Calcular días ignorar años y meses entre dos fechas
Si solo desea calcular la diferencia de días ignorando años y meses entre dos fechas como se muestra a continuación, aquí hay una fórmula que puede ayudarlo.
=DATEDIF(A5,B5,"md")
Prensa Participar clave para obtener el resultado.
A5 es la fecha de inicio y B5 es la fecha de finalización.
1.14 Calcular diferencia entre dos fechas y devolver años, meses y días
Si desea obtener la diferencia entre dos fechas y devolver xx años, xx meses y xx días como muestra la siguiente captura de pantalla, aquí también se proporciona una fórmula.
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
Prensa Participar clave para obtener el resultado.
A8 es la fecha de inicio y B8 es la fecha de finalización.
1.15 Calcular diferencia entre una fecha y hoy
Para calcular automáticamente la diferencia entre una fecha y hoy, simplemente cambie la fecha_finalización en las fórmulas anteriores a HOY(). Aquí tome para calcular la diferencia de días entre una fecha pasada y hoy como una instancia.
=DATEDIF(A11,TODAY(),"d")
Prensa Participar clave para obtener el resultado.
Note: si desea calcular la diferencia entre una fecha futura y la actual, cambie start_date a hoy y tome la fecha futura como end_date de esta manera:
=DATEDIF(TODAY(),A14,"d")
Tenga en cuenta que start_date debe ser menor que end_date en la función DATEDIF; de lo contrario, volverá a #NUM. valor de error
1.16 Calcular días laborables con o sin vacaciones entre dos fechas
A veces, es posible que deba contar el número de días laborables con o sin vacaciones entre dos fechas determinadas.
En esta parte, utilizará la función NETWORKDAYS.INTL:
Haga Clic en NETWORKDAYS.INTL conocer sus argumentos y uso.
Contar los días laborables con festivos
Para contar los días laborables con vacaciones entre dos fechas en las celdas A2 y B2, utilice la fórmula como esta:
=NETWORKDAYS.INTL(A2,B2)
Prensa Participar clave para obtener el resultado.
Contar los días laborables sin vacaciones
Para contar los días laborables con días festivos entre dos fechas en las celdas A2 y B2, y excluyendo los días festivos en el rango D5:D9, utilice la fórmula de la siguiente manera:
=DÍASLAB.INTL(A5,B5,1,D5:D9)
Prensa Participar clave para obtener el resultado.
Nota:
En las fórmulas anteriores, toman el sábado y el domingo como fin de semana, si tiene diferentes días de fin de semana, cambie el argumento [fin de semana] según lo necesite.
1.17 Calcular fines de semana entre dos fechas
Si quieres contar el número de fines de semana entre dos fechas, las funciones SUMPRODUCT o SUM pueden hacerte un favor.
Para contar los fines de semana (sábado y domingo) entre dos fechas en la celda A12 y B12:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
Prensa Participar clave para obtener el resultado.
1.18 Calcular 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 el lunes, entre dos fechas, la combinación de las funciones INT y WEEKDAY puede ayudarlo.
Las celdas A15 y B15 son las dos fechas entre las que desea contar el lunes, use la fórmula como esta:
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
Prensa Participar clave para obtener el resultado.
Cambie el número del día de la semana en la función WEEKDAY para contar un día de la semana 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 los días restantes en el mes/año
A veces, es posible que desee saber los días restantes del mes o del año en función de la fecha proporcionada, como muestra la siguiente captura de pantalla:
Obtener los días restantes del mes actual
Haga Clic en EOMONTH conocer el argumento y el uso.
Para obtener los días restantes del mes actual en la celda A2, utilice la fórmula como esta:
=EOMONTH(A2,0)-A2
Prensa Participar y arrastre el controlador de relleno automático para aplicar esta fórmula a otras celdas si es necesario.
Consejo: los resultados pueden mostrarse como formato de fecha, simplemente cámbielos a formato general o numérico.
Obtener los días restantes del año actual
Para obtener los días restantes del año actual en la celda A2, utilice la fórmula como esta:
=DATE(YEAR(A2),12,31)-A2
Prensa Participar y arrastre el controlador de relleno automático para aplicar esta fórmula a otras celdas si es necesario.
1.21 Calcular diferencia entre dos tiempos
Para obtener la diferencia entre dos tiempos, aquí hay dos fórmulas simples que pueden ayudarte.
Supongamos que en las celdas A2 y B2 contienen start_time y end_time por separado, usando las fórmulas como estas:
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
Prensa Participar clave para obtener el resultado.
Nota:
- Si usa end_time-start_time, puede formatear el resultado a otro formato de hora según lo necesite en el cuadro de diálogo Formato de celdas.
- Si usa TEXTO(hora_final-primera_hora,"formato_hora"), ingrese el formato de hora en el que desea que se muestre el resultado en la fórmula, como TEXTO(hora_final-primera_hora,"h") devuelve 16.
- Si end_time es menor que start_time, ambas fórmulas arrojan valores de error. Para resolver este problema, puede agregar ABS delante de estas fórmulas, como ABS (B2-A2), ABS (TEXTO (B2-A2, "hh: mm: ss")), luego formatear el resultado como tiempo.
1.22 Calcular la diferencia entre dos tiempos en horas/minutos/segundos
Si desea calcular la diferencia entre dos tiempos en horas, minutos o segundos como muestra la siguiente captura de pantalla, siga esta parte.
Obtener horas de diferencia entre dos tiempos
Para obtener la diferencia de horas entre dos tiempos en A5 y B5, utilice la fórmula como esta:
=INT((B5-A5)*24)
Prensa Participar clave, luego formatee el resultado del formato de hora como general o número.
Si desea obtener la diferencia de horas decimales, use (end_time-start_time)*24.
Obtener minutos de diferencia entre dos tiempos
Para obtener la diferencia de minutos entre dos tiempos en A8 y B8, utilice la fórmula como esta:
=INT((B8-A8)*1440)
Prensa Participar clave, luego formatee el resultado del formato de hora como general o número.
Si desea obtener la diferencia de minutos decimales, use (end_time-start_time)*1440.
Obtener segundos de diferencia entre dos tiempos
Para obtener la diferencia de segundos entre dos tiempos en A5 y B5, utilice la fórmula como esta:
=(B11-A11)*86400)
Prensa Participar clave, luego formatee el resultado del formato de hora como general o número.
1.23 Calcular la diferencia de horas solo entre dos tiempos (no exceder las 24 horas)
Si la diferencia entre dos horas no supera las 24 horas, la función HORA puede obtener rápidamente la diferencia de horas entre estas dos horas.
Haga Clic en HORA para más detalles sobre esta función.
Para obtener la diferencia de horas entre las horas en la celda A14 y B14, use la función HORA de esta manera:
=HOUR(B14-A14)
Prensa Participar clave para obtener el resultado.
start_time debe ser menor que end_time, de lo contrario, la fórmula devuelve #NUM! valor de error
1.24 Calcule la diferencia de minutos solo entre dos tiempos (no exceda los 60 minutos)
La función MINUTO puede obtener rápidamente la única diferencia de minutos entre estas dos horas e ignorar las horas y los segundos.
Haga Clic en MINUTO para más detalles sobre esta función.
Para obtener solo la diferencia de minutos entre las horas en la celda A17 y B17, use la función MINUTO de esta manera:
=MINUTE(B17-A17)
Prensa Participar clave para obtener el resultado.
start_time debe ser menor que end_time, de lo contrario, la fórmula devuelve #NUM! valor de error
1.25 Calcule la diferencia de segundos solo entre dos tiempos (no exceda los 60 segundos)
La función SEGUNDO puede obtener rápidamente la única diferencia de segundos entre estas dos horas e ignorar las horas y los minutos.
Haga Clic en SEGUNDO para más detalles sobre esta función.
Para obtener solo la diferencia de segundos entre los tiempos en la celda A20 y B20, use la función SEGUNDO de esta manera:
=SECOND(B20-A20)
Prensa Participar clave para obtener el resultado.
start_time debe ser menor que end_time, de lo contrario, la fórmula devuelve #NUM! valor de error
1.26 Calcula la diferencia entre dos tiempos y devuelve horas, minutos, segundos
Si desea mostrar la diferencia entre dos horas como xx horas xx minutos xx segundos, utilice la función TEXTO como se muestra a continuación:
Haga Clic en TEXTO para darse cuenta de los argumentos y el uso de esta función.
Para calcular la diferencia entre los tiempos en la celda A23 y B23, use la fórmula como esta:
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
Prensa Participar clave para obtener el resultado.
Nota:
Esta fórmula solo calcula la diferencia de horas que no exceda las 24 horas, y end_time debe ser mayor que start_time, de lo contrario, devuelve #¡VALOR! valor de error
1.27 Calcular la diferencia entre dos fechas y horas
Si hay dos horas en formato mm/dd/aaaa hh:mm:ss, para calcular la diferencia entre ellas, puede usar una de las siguientes fórmulas según lo necesite.
Obtenga la diferencia de tiempo entre dos fechas y horas y devuelva el resultado en formato hh: mm: ss
Tome dos fechas y horas en la celda A2 y B2 como ejemplo, use la fórmula como esta:
=B2-A2
Prensa Participar clave, devolviendo un resultado en formato de fecha y hora, luego formatee este resultado como [h]: mm: ss en la categoría personalizada bajo el Número pestaña en Formato de celdas diálogo.
Obtenga la diferencia entre dos fechas y horas y días de retorno, horas, minutos, segundos
Tome dos fechas y horas en la celda A5 y B5 como ejemplo, use la fórmula como esta:
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
Prensa Participar clave para obtener el resultado.
Nota: en ambas fórmulas, end_datetime debe ser mayor que start_datetime, de lo contrario, las fórmulas arrojan valores de error.
1.28 Calcula la diferencia horaria con milisegundos
En primer lugar, debe saber cómo formatear la celda para mostrar los milisegundos:
Seleccione las celdas que desea mostrar milisegundos y derecho para seleccionar Formato de celdas para habilitar el Formato de celdas diálogo, seleccionar Personalizado existentes Categoría lista en la pestaña Número, y escriba esto hh: mm: ss.000 en el cuadro de texto.
Utilice la fórmula:
Aquí para calcular la diferencia entre dos veces en la celda A8 y B8, use la fórmula como:
=ABS(B8-A8)
Prensa Participar clave para obtener el resultado.
1.29 Calcular horas de trabajo entre dos fechas excluyendo fines de semana
A veces, es posible que necesite contar las horas de trabajo entre dos fechas, excluyendo los fines de semana (sábado y domingo).
Aquí, las horas de trabajo se fijan en 8 horas todos los días, y para calcular las horas de trabajo entre dos fechas proporcionadas en las celdas A16 y B16, utilice la fórmula como esta:
=NETWORKDAYS(A16,B16) * 8
Prensa Participar clave y luego formatee el resultado como general o número.
Para obtener más ejemplos sobre cómo calcular las horas de trabajo entre dos fechas, visite Obtenga horas de trabajo entre dos fechas en Excel
Si tiene Kutools for Excel instalado en Excel, el 90 por ciento 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 Data & Time Helper
Para calcular la diferencia entre dos fechas y horas en Excel, solo el Asistente de fecha y hora es suficiente.
1. Seleccione una celda donde coloque el resultado calculado y haga clic en Kutools > Ayudante de fórmula > Asistente de fecha y hora.
2. En el estallido Asistente de fecha y hora cuadro de diálogo, siga la siguiente configuración:
- cheque Diferencias opción;
- Seleccione la fecha y hora de inicio y la fecha y hora de finalización en Entrada de argumentos sección, también puede ingresar directamente la fecha y la hora manualmente en el cuadro de entrada, o hacer clic en el icono del calendario para seleccionar la fecha;
- Seleccione el tipo de resultado de salida de la lista desplegable;
- Vista previa del resultado en Resultado .
3. Hacer clic en Ok. Se genera el resultado calculado y arrastre el controlador de autocompletar sobre las celdas que también necesita calcular.
Consejo:
Si desea obtener la diferencia entre dos fechas y horas y mostrar el resultado como días, horas y minutos con Kutools para Excel, haga lo siguiente:
Seleccione una celda donde desee colocar el resultado y haga clic en Kutools > Ayudante de fórmula > Fecha y hora > Contar días, horas y minutos entre dos fechas.
Entonces en el Ayudante de fórmulas cuadro de diálogo, especifique la fecha de inicio y la fecha de finalización, luego haga clic en Ok.
Y el resultado de la diferencia se mostrará como días, horas y minutos.
Haga Clic en Asistente de fecha y hora para saber más sobre el uso de esta característica.
Haga Clic en Kutools for Excel para conocer todas las características de este complemento.
Haga Clic en Descargar gratis para obtener una prueba gratuita de 30 días de Kutools para Excel
Si desea contar rápidamente el fin de semana, los días laborables o un día de la semana específico entre dos fechas y horas, Kutools for Excel's Ayudante de fórmula el grupo te puede ayudar.
1. Seleccione la celda que colocará el resultado calculado, haga clic en Kutools > Estadístico > Número de días no laborables entre dos fechas/Número de días laborables entre dos fechas/Cuente el número de días específicos de la semana.
2. En el estallido Ayudante de fórmulas cuadro de diálogo, especifique la fecha de inicio y la fecha de finalización, si aplica Cuente el número de días específicos de la semana., también debe especificar el día de la semana.
Para contar el día de la semana específico, puede consultar la nota para usar 1-7 para indicar domingo-sábado.
3. Hacer clic en Oky luego arrastre el controlador de autocompletar sobre las celdas que necesitan contar el número de fines de semana/días laborales/días de la semana específicos si es necesario.
Haga Clic en Kutools for Excel para conocer todas las características de este complemento.
Haga Clic en Descargar gratis para obtener una prueba gratuita de 30 días de Kutools para Excel
2. Sumar o restar fecha y hora
Excepto para calcular la diferencia entre dos fechas y horas, sumar o restar también es el cálculo normal de fechas y horas en Excel. Por ejemplo, es posible que desee obtener la fecha de vencimiento en función de la fecha de producción y la cantidad 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í hay dos métodos diferentes.
Supongamos que agrega 21 días a una fecha en la celda A2, elija uno de los métodos a continuación para resolverlo,
Método 1 fecha+días
Seleccione una celda y escriba la fórmula:
=A+21
Prensa Participar clave para obtener el resultado.
Si desea restar 21 días, simplemente cambie el signo más (+) por el signo menos (-).
Método 2 Pegado Especial
1. Escriba la cantidad de días que desea agregar en una celda suponiendo en la celda C2, y luego presione Ctrl + C para copiarlo
2. Luego seleccione las fechas que desea agregar 21 días, haga clic derecho para mostrar el menú contextual y seleccione Pegado especial....
3. En el Pegado especial diálogo, comprobar Añada (Si desea restar días, marque Sustraer opción). Hacer clic OK.
4. Ahora las fechas originales cambian a números de 5 dígitos, formatéelas 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.
Haga Clic en EDATE para estudiar sus argumentos y uso.
Supongamos que agrega 6 meses a la fecha en la celda A2, use la fórmula como esta:
=EDATE(A2,6)
Prensa Participar clave para obtener el resultado.
Si desea restar 6 meses a la fecha, cambie 6 a -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 combina las funciones FECHA, AÑO, MES y DÍA.
Supongamos que agrega 3 años a la fecha en la celda A2, use la fórmula como esta:
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
Prensa Participar clave para obtener el resultado.
Si desea restar 3 años a la fecha, cambie 3 a -3.
2.14 Sumar o restar semanas a una fecha
Para sumar o restar semanas a una fecha, la fórmula general es
Supongamos que agrega 4 semanas a la fecha en la celda A2, use la fórmula como esta:
=A2+4*7
Prensa Participar clave para obtener el resultado.
Si desea restar 4 semanas a la fecha, cambie 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 presenta cómo usar la función WORKDAY para sumar o restar días hábiles a una fecha determinada, excluyendo o incluyendo días festivos.
Visite WORKDAY para conocer más detalles sobre sus argumentos y uso.
Añadir días laborables incluyendo festivos
En la celda A2 está la fecha que usa, en la celda B2 contiene la cantidad de días que desea agregar, use la fórmula como esta:
=WORKDAY(A2,B2)
Prensa Participar clave para obtener el resultado.
Añadir días laborables sin festivos
En la celda A5 está la fecha que usa, en la celda B5 contiene la cantidad de días que desea agregar, en el rango D5: D8 enumera los días festivos, use la fórmula como esta:
=WORKDAY(A5,B5,D5:D8)
Prensa Participar clave para obtener el resultado.
Nota:
La función WORKDAY toma el sábado y el domingo como fines de semana, si sus fines de semana son los sábados y domingos, puede aplicar la función WOKRDAY.INTL, que admite la especificación de fines de semana.
Visite DÍA LABORAL.INTL para más información.
Si desea restar días laborables a una fecha, simplemente cambie el número de días a negativo en la fórmula.
2.16 Sumar o restar año, mes, días específicos a una fecha
Si desea agregar un año, mes y día específicos a una fecha, la fórmula que combina la función FECHA, AÑO, MES y DÍAS puede hacerle un favor.
Para agregar 1 año, 2 meses y 30 días a una fecha en A11, use la fórmula como esta:
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
Prensa Participar clave para obtener el resultado.
Si desea restar, cambie todos los signos más (+) por signos menos (-).
2.21 Sumar o restar horas/minutos/segundos a una fecha y hora
Aquí proporciona algunas fórmulas para sumar o restar horas, minutos o segundos a una fecha y hora.
Agregar o restar horas a una fecha y hora
Supongamos que agrega 3 horas a una fecha y hora (también puede ser una hora) en la celda A2, use la fórmula como esta:
=A2+3/24
Prensa Participar clave para obtener el resultado.
Agregar o restar horas a una fecha y hora
Supongamos que agrega 15 minutos a una fecha y hora (también puede ser una hora) en la celda A5, use la fórmula como esta:
=A2+15/1440
Prensa Participar clave para obtener el resultado.
Agregar o restar horas a una fecha y hora
Supongamos que agrega 20 segundos a una fecha y hora (también puede ser una hora) en la celda A8, use la fórmula como esta:
=A2+20/86400
Prensa Participar clave para obtener el resultado.
2.22 Suma de tiempos en 24 horas
Supongamos que hay una tabla de Excel que registra el tiempo de trabajo de todo el personal en una semana, para sumar el tiempo de trabajo total para calcular los pagos, puede usar Rango suma) para obtener el resultado. Pero, en general, el resultado total se mostrará como un tiempo que no exceda las 24 horas, como muestra la siguiente captura de pantalla. ¿Cómo puede obtener el resultado correcto?
En realidad, solo necesita formatear el resultado como [hh]: mm: ss.
Haga clic con el botón derecho en la celda de resultados, elija Formato de celdas en el menú contextual, y en la ventana emergente Formato de celdas diálogo, elegir Personalizado de la lista y escriba [hh]: mm: ss en el cuadro de texto en la sección derecha, haga clic en OK.
El resultado sumado se mostrará correctamente.
2.23 Añadir horas de trabajo a una fecha excluyendo fines de semana y festivos
Aquí se proporciona una fórmula larga para obtener la fecha de finalización basada en agregar un número específico de horas de trabajo a una fecha de inicio y excluye los fines de semana (sábado y domingo) y días festivos.
En una tabla de Excel, A11 contiene la fecha y hora de inicio y B11 contiene las horas de trabajo, en las celdas E11 y E13 están las horas de inicio y fin de trabajo, y la celda E15 contiene las vacaciones que se excluirán.
Utilice la fórmula como esta:
=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))
Prensa Participar clave para obtener el resultado.
Si tiene instalado Kutools for Excel, solo una herramienta: Ayuda de fecha y horar puede resolver la mayoría de los cálculos al sumar y restar fecha y hora.
1. Haga clic en una celda en la que desee generar el resultado y aplique esta herramienta haciendo clic en Kutools > Ayudante de fórmula > Ayudante de fecha y hora.
2. En el Asistente de fecha y hora diálogo, comprobar Añada Opción o Sustraer opción que necesite, luego elija la celda o escriba directamente la fecha y hora que desea usar en Entrada de argumentos sección, luego especifique los años, meses, semanas, días, horas, minutos y segundos que desea sumar o restar, luego haga clic en Ok. Ver captura de pantalla:
Puede obtener una vista previa del resultado calculado en el Resultado .
Ahora se muestra el resultado, arrastre el controlador automático sobre otras celdas para obtener los resultados.
Haga Clic en Asistente de fecha y hora para saber más sobre el uso de esta característica.
Haga Clic en Kutools for Excel para conocer todas las características de este complemento.
Haga Clic en Descarga de tarifas para obtener una prueba gratuita de 30 días de Kutools para Excel
2.41 Marcar o resaltar si una fecha ha caducado
Si hay una lista de fechas de caducidad de los productos, es posible que desee comprobar y resaltar las fechas que caducan en función de hoy, como se muestra en la siguiente captura de pantalla.
En realidad, el Formato condicional puede manejar rápidamente este trabajo.
1. Seleccione las fechas que desea verificar, luego haga clic en Inicio > Formato condicional > Nueva regla.
2. En el Nueva regla de formato diálogo, seleccionar Use una fórmula para determinar qué celdas formatear existentes Seleccione un tipo de regla sección y tipo =B2 en el cuadro de entrada (B2 es la primera fecha que desea verificar) y haga clic en Formato aparecer Formato de celdas cuadro de diálogo, luego elija un formato diferente para resaltar las fechas de vencimiento según lo necesite. Hacer clic OK > OK.
2.42 Devuelve el final del mes actual/primer día del próximo mes/a>
Las fechas de vencimiento de algunos productos son al final del mes de producción o el primer día del próximo mes de producción, para enumerar rápidamente las fechas de vencimiento según la fecha de producción, siga esta parte.
Obtener el final del mes actual
Aquí hay una fecha de producción en la celda B13, use la fórmula como esta:
=EOMONTH(B13,0)
Prensa Participar clave para obtener el resultado.
Obtener el primer día del próximo mes
Aquí hay una fecha de producción en la celda B18, use la fórmula como esta:
=EOMONTH(B18,0)+1
Prensa Participar clave para obtener el resultado.
3. Calcula la edad
En esta sección, enumera los métodos para resolver cómo calcular la edad en función de una fecha determinada o un número de serie.
3.11 Calcular la edad según la fecha de nacimiento dada
Obtenga la edad en número decimal según la fecha de nacimiento
Haga Clic en YEARFRAC para obtener detalles sobre sus argumentos y el uso.
Por ejemplo, para obtener las edades según la lista de fechas de nacimiento en la columna B2:B9, utilice la fórmula como esta:
=YEARFRAC(B2,TODAY())
Prensa Participar luego arrastre el controlador de autocompletar hacia abajo hasta que se calculen todas las edades.
Consejo:
1) Puede especificar el lugar decimal que necesite en el Formato de celdas diálogo.
2) Si desea calcular la edad en una fecha específica en función de una fecha de nacimiento dada, cambie HOY() a la fecha específica entre comillas dobles como =FRAC DEL AÑO(B2, "1/1/2021")
3) Si desea obtener la edad del próximo año en función de la fecha de nacimiento, simplemente agregue 1 en la fórmula como =YEARFRAC(B2,TODAY())+1.
Obtenga la edad en números enteros según la fecha de nacimiento
Haga Clic en SIFECHA para obtener detalles sobre sus argumentos y el uso.
Usando el ejemplo anterior, para obtener la edad según las fechas de nacimiento en la lista en B2:B9, use la fórmula como esta:
=DATEDIF(B2,TODAY(),"y")
Prensa Participar luego arrastre el controlador de autocompletar hacia abajo hasta que se calculen todas las edades.
Consejo:
1) Si desea calcular la edad en una fecha específica en función de una fecha de nacimiento determinada, cambie HOY() a la fecha específica entre comillas dobles, como =SIFECHA(B2,"1/1/2021","y") .
2) Si desea obtener la edad del próximo año en función de la fecha de nacimiento, simplemente agregue 1 en la fórmula como = DATEDIF (B2, TODAY (), "y") + 1.
3.12 Calcular la edad en formato de años, meses y días por cumpleaños dado
Si desea calcular la edad en función de una fecha de nacimiento dada y mostrar el resultado como xx años, xx meses, xx días como muestra la siguiente captura de pantalla, aquí hay una fórmula larga que puede ayudarlo.
Para obtener la edad en años, meses y días según la fecha de nacimiento en la celda B12, utilice la fórmula como esta:
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
Prensa Participar para obtener la edad, luego arrastre el controlador de autocompletar hacia abajo a otras celdas.
Consejo:
Si desea calcular la edad en una fecha específica en función de una fecha de nacimiento dada, cambie HOY() a la fecha específica entre comillas dobles como = =DATEDIF(B12,"1/1/2021","Y")& " Años, "&SIFECHA(B12,"1/1/2021","YM")&" Meses, "&SIFECHA(B12,"1/1/2021","MD")&" Días".
3.13 Calcular la edad por nacimiento de fecha anterior al 1/1/1900
En Excel, la fecha anterior al 1/1/1900 no se puede ingresar como una fecha y hora ni calcularse correctamente. Pero si desea calcular la edad de una persona famosa según la fecha de nacimiento dada (antes del 1/11900) y la fecha de muerte, solo un código VBA puede ayudarlo.
1. Prensa otro + F11 claves para habilitar Microsoft Visual Basic para aplicaciones ventana y haga clic en recuadro pestaña y elegir Módulo para crear un nuevo módulo.
2. Luego copie y pegue el siguiente código en el nuevo módulo.
VBA: calcula la edad antes del 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, regrese a la hoja y seleccione una celda para colocar la edad calculada, escriba =AgeFunc(birthdate,deathdate), en este caso, =FuncionEdad(B22,C22), presione la tecla Enter para obtener la edad. Y use el controlador de autocompletar para aplicar esta fórmula a otras celdas si es necesario.
Si tiene Kutools for Excel instalado en Excel, puede aplicar el Asistente de fecha y hora Herramienta para calcular la edad.
1. Seleccione una celda en la que desee colocar la edad calculada y haga clic en Kutools > Ayudante de fórmula > Asistente de fecha y hora.
2. En el Asistente de fecha y hora diálogo,
- 1) Comprobar Edad opción;
- 2) Elija la celda de fecha de nacimiento o ingrese directamente la fecha de nacimiento o haga clic en el ícono de calendario para seleccionar la fecha de nacimiento;
- 3) Elija Hoy opción si desea calcular la edad actual, elija Fecha específica e ingrese la fecha si desea calcular la edad en el pasado o en el futuro;
- 4) Especifique el tipo de salida de la lista desplegable;
- 5) Vista previa del resultado de salida. Hacer clic Ok.
Haga Clic en Asistente de fecha y hora para saber más sobre el uso de esta característica.
Haga Clic en Kutools for Excel para conocer todas las características de este complemento.
Haga Clic en Descargar gratis para obtener una prueba gratuita de 30 días de Kutools para Excel
3.31 Obtener cumpleaños del número de identificación
Si hay una lista de números de identificación que usan los primeros 6 dígitos para registrar la fecha de nacimiento, como 920315330 significa que la fecha de nacimiento es 03/15/1992, ¿cómo puede obtener rápidamente la fecha de nacimiento en otra columna?
Ahora tomemos la lista de números de identificación que comienzan en la celda C2 como una instancia y usemos la fórmula como esta:
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
Prensa Participar llave. A continuación, arrastre el controlador de autocompletar hacia abajo para obtener otros resultados.
Nota:
En la fórmula, puede cambiar la referencia a su necesidad. Por ejemplo, si el número de ID se muestra como 13219920420392, el cumpleaños es el 04/20/1992, puede 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 hay una lista de números de identificación que usan los primeros 6 dígitos para registrar la fecha de nacimiento, como 920315330 significa que la fecha de nacimiento es 03/15/1992, ¿cómo puede calcular rápidamente la edad según cada número de identificación en Excel?
Ahora tomemos la lista de números de identificación que comienzan en la celda C2 como una instancia y usemos la fórmula como esta:
=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")
Prensa Participar llave. A continuación, arrastre el controlador de autocompletar 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 con 20, por ejemplo, 200203943 se considerará como el año 2020; si el año es mayor que el año actual, se considerará que el año comienza con 19, por ejemplo, 920420392 se considerará como el año 1992.
Más tutoriales de Excel:
Combine varios libros de trabajo/hojas de trabajo en uno
Este tutorial enumera casi todos los escenarios de combinación que puede enfrentar y le proporciona soluciones profesionales relativas.
Dividir celdas de texto, número y fecha (separadas 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 ayudarlo a saber cómo manejar el trabajo de división cuando se encuentra con el mismo problema.
Combine el contenido de varias celdas sin perder datos en Excel
Este tutorial limita 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.
Compare dos columnas para coincidencias y diferencias en Excel
Aquí, este artículo cubre la mayoría de los escenarios posibles de la comparación de dos columnas que podría encontrar, y espero que pueda ayudarlo.
Las mejores herramientas de productividad de oficina
Kutools para Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%
- Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
- Combinar celdas / filas / columnas y conservación de datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir celdas duplicadas; Comparar rangos...
- Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
- Copia exacta Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
- Fórmulas favoritas e insertar rápidamente, Rangos, gráficos e imágenes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
- Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
- Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
- Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
- Agrupación de tablas dinámicas por número de semana, día de la semana y más ... Mostrar celdas bloqueadas y desbloqueadas por diferentes colores; Resalte las celdas que tienen fórmula / nombre...
- 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 en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
