admin
Si usa Excel regularmente, seguro que se ha topado con fechas y veces en tus celdas. Los datos a menudo tienen un registro de cuándo se crearon o actualizaron, por lo que es esencial saber cómo trabajar con estos datos.
Aquí hay tres habilidades clave que aprenderá en este tutorial:
- Cómo fechas de formato en Excel para que aparezcan en tu estilo preferido
- Fórmulas para calcular el número de días, meses y años entre dos fechas
- Una fórmula de fecha de Excel para registrar la fecha de hoy y un atajo de teclado para agregar la hora actual
Microsoft Excel básicamente puede hacer cualquier cosa con los datos, si sabe cómo hacerlo. Este tutorial es otro paso clave para agregar habilidades a su cinturón de herramientas de Excel. Empecemos.
Fórmulas de fecha y hora de Excel (tutorial rápido en video)
Este screencast lo guiará a través de cómo trabajar con fechas y horas en Excel. Cubro el formato de fechas a diferentes estilos, así como fórmulas de fecha de Excel para calcular y trabajar con fechas. Asegúrese de descargar el libro de Excel gratuito con ejercicios que he adjuntado a este tutorial.
Siga leyendo para obtener una guía de referencia escrita sobre cómo dar formato a fechas y horas en Excel, y trabajar con ellas en sus fórmulas. Incluso compartiré varios consejos que no se cubrieron en el screencast.
Escribir fechas y horas en Excel
Para esta parte del tutorial, use la pestaña titulada «Escribir fechas y horas» en el libro de trabajo de ejemplo.
Una de las claves para trabajar con fechas y horas en Excel es capturar los datos correctamente. Aquí se explica cómo escribir fechas y horas en sus hojas de cálculo de Excel:
1. Cómo escribir fechas
Recomiendo escribir las fechas en el mismo formato que usa su sistema. Para nuestros lectores estadounidenses, la fecha completa estaría en el «día mes año» formato. Las fechas de estilo europeo son «mes día año.»
Cuando escribo fechas, siempre escribo la fecha completa con el mes, el día y el año. Si solo quiero mostrar el mes y el año, simplemente lo formatearé de esa manera (más sobre eso en un minuto).
2. Cómo escribir tiempos
Es fácil escribir tiempos en Excel. Podemos especificar cualquier cosa, desde solo una hora del día, hasta el segundo exacto en que ocurrió algo.
Si quisiera registrar la hora como 4 p. m., escribiría «4 p. m.» en una celda de Excel y luego presionaría Intro:
Observe que una vez que presionamos Intro, Excel convierte lo que hemos escrito en un formato de datos de horas: minutos: segundos.
Aquí le mostramos cómo registrar un tiempo más específico en su hoja de cálculo:
La clave es usar dos puntos para separar la sección de los datos de tiempo y luego agregar un espacio más «AM» o «PM».
3. Cómo escribir fecha y hora juntos
También puede escribir combinaciones de fechas y horas en Excel para marcas de tiempo muy específicas.
Para escribir una combinación de fecha y hora, simplemente use lo que ya aprendimos sobre escribir fechas y escribir horas.
Tenga en cuenta que Excel ha convertido la hora a un formato de 24 horas cuando se usa junto con una fecha, de forma predeterminada. Si quieres cambiar el estilo de esta fecha, sigue leyendo.
Bonificación: atajo de teclado de Excel para la hora actual
Uno de mis métodos abreviados de teclado favoritos de Excel inserta la hora actual en una hoja de cálculo. Uso esta fórmula a menudo, cuando apunto la hora en que hice un cambio en mis datos. Pruébalo:
Control + Shift + ;
Formateo de fechas en Excel
Para esta parte del tutorial, use la pestaña titulada «Formato de fechas y horas» en el libro de trabajo de ejemplo.
¿Qué puedes hacer cuando tus citas son fechas de estilo europeo? Es decir, están en un formato de día-mes-año, ¿y necesita convertirlos al formato más familiar de mes-día-año?
En la captura de pantalla anterior, lo que podría sorprenderte es que las seis celdas contienen exactamente los mismos datos: «22/1/2017». Lo que difiere es cómo están formateados en Excel. Los datos originales son idénticos, pero se pueden formatear para que se muestren de varias formas.
En la mayoría de los casos, es mejor usar formato para modificar el estilo de nuestras fechas. No necesitamos modificar los datos en sí, solo cambiar la forma en que se presentan.
Formato de celdas de Excel
Para cambiar la apariencia de nuestros datos de fecha y hora, asegúrese de que está trabajando en el Casa ficha de Excel. Sobre el Cinta (menú en la parte superior de Excel), busque la sección etiquetada Número.
Hay una pequeña flecha en la esquina inferior derecha de la sección. Haga clic para abrir el Menú Formato de celdas.
los Formato de celdas El menú tiene una variedad de opciones para diseñar sus fechas y horas. Puede convertir «22/01/2017» en «Domingo, 22 de enero» con solo formatear. Luego, puede tomar el pintor de formato y cambiar todos los estilos de celda.
Dedique algún tiempo a explorar este menú y probar los diferentes estilos para las fechas y horas de Excel.
Obtener datos de fechas y horas
Digamos que tenemos una lista de datos que tiene fechas y horas muy específicas y queremos obtener versiones más simples de esas fórmulas. Tal vez tengamos una lista de fechas de transacciones exactas, pero queremos trabajar con ellas a un nivel superior, agrupándolas por año o mes.
Puede obtener el año a partir de una fecha con esta fórmula de Excel:
=YEAR(CELL)
Para obtener solo el mes de una celda de fecha, use la siguiente fórmula de Excel:
=MON(CELL)
Encuentra la diferencia entre fechas y horas
Para esta parte del tutorial, use la pestaña titulada «SIFECHA» en el libro de trabajo de ejemplo.
Si bien los formatos se utilizan para cambiar la forma en que se presentan las fechas y las horas, fórmulas en Excel se utilizan para modificar, calcular o trabajar con fechas y horas programáticamente.
los SIFECHA La fórmula es poderosa para calcular las diferencias entre días. Dé a la fórmula dos fechas y devolverá la cantidad de días, meses y años entre dos fechas. Veamos cómo usarlo.
1. Días entre fechas
Esta fórmula de fecha de Excel calculará el número de días entre dos fechas:
=DATEDIF(A1,B1,"d")
La fórmula toma dos celdas, separadas por comas, y luego usa una «d» para calcular la diferencia en días.
Aquí hay algunas ideas sobre cómo podría usar esta fórmula de fecha de Excel para su ventaja:
- Calcula la diferencia entre hoy y tu cumpleaños para iniciar una cuenta regresiva de cumpleaños
- Use un DATEDIF para calcular la diferencia entre dos fechas y divida el crecimiento de su cartera de acciones por la cantidad de días para calcular el crecimiento (¡o la pérdida!) por día
2. Meses entre fechas
DATEDIF también calcula el número de meses entre dos fechas. Esta fórmula de fecha en Excel es muy similar, pero sustituye una «m» por «d» para calcular la diferencia en meses:
=DATEDIF(A1,B1,"m")
Sin embargo, hay una peculiaridad en la forma en que Excel aplica DATEDIF: calcula meses enteros entre fechas. Vea la captura de pantalla a continuación.
Para mí, hay tres meses entre el 1 de enero y el 31 de marzo (todo enero, todo febrero y casi todo marzo). Sin embargo, debido a que Excel usa meses completos, solo considera enero y febrero como meses completos, por lo que el resultado es «2».
Esta es mi forma preferida de calcular el número de meses entre dos fechas. Encontraremos la diferencia de fechas en días y luego la dividiremos por la cantidad promedio de días en un mes: 30.42.
=(DATEDIF(A1,B1,"d")/30.42)
Apliquemos nuestro DATEDIF modificado a dos fechas:
Mucho mejor. El resultado de 2,99 está muy cerca de 3 meses completos y será mucho más útil en fórmulas futuras.
La documentación oficial de Excel tiene un método complejo para calcular meses entre fechas, pero esta es una manera simple y fácil de acercarse bastante. Escribir una buena fórmula de Excel se trata de encontrar el punto óptimo de precisión y simplicidad, y esta fórmula hace ambas cosas.
3. Años entre fechas
Finalmente, calculemos el número de años entre dos fechas. La forma oficial de calcular años entre fechas es con la siguiente fórmula:
=DATEDIF(A1,B1,"y")
Tenga en cuenta que esto es lo mismo que nuestras fórmulas SIFECHA anteriores, pero simplemente hemos sustituido la última parte de la fórmula con «y» para calcular la cantidad de años entre dos fechas. Veámoslo en acción:
Note que esto funciona como el DATEDIF por meses: cuenta solamente años completos que han pasado. Preferiría incluir también los años parciales que pasan. Aquí hay un mejor DATEDIF por años:
=(DATEDIF(A1,B2,"d")/365)
Básicamente, solo obtenemos la diferencia de fechas en días y luego la dividimos por 365 para calcularla como un año. Aquí están los resultados:
DATEDIF es extremadamente poderoso, pero tenga cuidado con su funcionamiento: solo calculará los meses o años completos que hayan pasado de forma predeterminada. Use mis versiones modificadas para mayor precisión en los resultados.
Bono: Días Laborables Entre Fechas
Las fórmulas de fecha de Excel cubiertas anteriormente se centran en la cantidad de días hábiles entre fechas. Sin embargo, a veces es útil simplemente calcular la cantidad de días laborables (básicamente, días de la semana) entre dos fechas.
En este caso, usaremos =DIAS LABORABLES para calcular el número de días laborables entre dos fechas.
=NETWORKDAYS(A1,B1)
En la captura de pantalla a continuación, muestro un ejemplo del uso de NETWORKDAYS. Puede ver el calendario que muestra cómo la fórmula calculó un resultado de «4».
Si tiene vacaciones conocidas en el período de tiempo que desea excluir, consulte la documentación oficial de NETWORKDAYS.
Recapitular y seguir aprendiendo
Las fechas y horas son omnipresentes en las hojas de cálculo. Las fórmulas de fecha de Excel y las opciones de formato son útiles. Las técnicas de este tutorial pueden llevar sus habilidades de Excel al siguiente nivel para que pueda incorporar datos basados en fechas sin problemas en sus hojas de cálculo.
Ha agregado una habilidad a su caja de herramientas de Excel: ¿por qué detenerse aquí? Encadene fórmulas y habilidades de Excel para crear poderosas hojas de cálculo. Para seguir aprendiendo más sobre cómo trabajar con hojas de cálculo de Excel, consulte estos otros recursos:
- Las declaraciones IF son lógica integrada en su hoja de cálculo que muestra resultados basados en condiciones. Puede combinar una declaración IF con un rango de fechas para mostrar datos basados en una fecha u hora. Echa un vistazo a este tutorial para aprenderlos.
- Puede combinar el tutorial BUSCARV con fórmulas de fecha y hora para hacer coincidir valores basados en una fecha u hora.
Buscar y eliminar duplicados es una función de Excel que se usa en combinación con datos de fecha y hora, ya que a menudo es uno de los mejores datos para buscar duplicados.
¿Cómo trabaja con los datos de calendario y tiempo en sus hojas de cálculo de Excel? ¿Hay alguna fórmula de fecha u hora de Excel que me falte en este tutorial que use con regularidad? Si es así, házmelo saber en los comentarios.
