Cómo calcular fechas de vencimiento y crear alertas en Excel
Tutorial paso a paso: Aprende a crear un sistema de Excel que calcule fechas de vencimiento y que además muestre una alerta automática. Puedes descargar el archivo de ejemplo para que puedas practicar a la par con el vídeo.
🥇 Cómo crear una Tabla de Alertas
Comenzaremos estructurando nuestra tabla, la cual tendrá una formula que nos indique la fecha actual (1), la cual siempre se actualizara con los días, y los campos como se muestra en (2), principalmente los campos de Fecha de Emisión, Días a Vencer y Fecha de Vencimiento. Estos campos nos servirán para poder a partir de ellos y con el uso de formulas poder crear nuestras alertas de vencimiento
- En la celda H4 insertaremos una formula de resta de fechas, Fecha de Vencimiento – Fecha de Emisión (1).
Como estamos trabajando en una tabla solo necesitaremos llenar una de las celdas del campo días, al dar Enter se llenara automáticamente a las demás celdas.
🥈 Formato condicional de celdas
Luego para hacer mas visible los vencimientos de las fechas, necesitamos dar un Formato Condicional para facilitar la visualización de estas alarmas, para esto seguimos los pasos a continuación:
- Seleccionamos el rango de G4:G11 (1).
- En la pestaña de Inicio, en el apartado Estilos damos clic en Formato Condicional (2).
- Damos clic en Nueva Regla (3), se la ventana de Nueva regla de formato (4).
- Seleccionamos la opción señalada en (1).
- En (2), escribiremos las formulas para poder dar la condicional.
- Damos clic en Formato (3), luego seleccionamos Relleno (4).
- En Color de Fondo (5), seleccionamos los colores de alerta.
- Finalmente damos clic en Aceptar (6).
- En (1), se muestra las formulas que debemos insertar para cada condicional.
- En (2), se indica los colores que se uso como relleno de cada condicional.
FORMULAS DE LAS CONDICIONES USADAS
Condición 1 | =$H4>=4 |
Condición 2 | =Y($H4>0;$H4<4) |
Condición 3 | =$H4=0 |
Condición 4 | =$H4<0 |
🥉 Fórmula para texto de alerta en Excel
Ya creada las condicionales para cada alerta, ahora insertaremos una formula (1) que nos muestre un texto segun las diferentes condicionales que hemos creado.
FÓRMULA UTILIZADA EN EL EJEMPLO
=SI(E4="";"VACIO";SI(H4<0;"Vencido hace "&H4*-1&" días";SI(H4=0;"Vence hoy";SI(H4<4;"Tiene "&H4&" días";"Faltan "&H4&" días"))))
Ya realizado los pasos anteriores, así debería quedarnos nuestra tabla de alertas.
🏅 Código de mensaje de alerta
Para poder insertar un mensaje que se muestre cada vez que abrimos el archivo de Excel, primero debemos crear un contador de si tenemos fechas vencidas, si vencen hoy o prontas a vencer; como se muestra en (1).
FORMULAS USADAS PARA LOS CONTADORES
D2 = Vencidos | =CONTAR.SI(Tabla1[días];»<0″) |
E2 = Vencen Hoy | =CONTAR.SI(Tabla1[días];»=0″) |
F2 = Entre 1 a 3 días | =CONTAR.SI.CONJUNTO(Tabla1[días];»>0″;Tabla1[días];»<4″) |
G2 = Mas de 3 días | =CONTAR.SI(Tabla1[días];»>3″) |
Abriremos la ventana de Código de VBA y escribiremos unas líneas de código sencillas, sigue los pasos a continuación:
- Para abrir la Ventana de VBA, presionamos las Teclas de Alt + F11.
- En el apartado de proyecto, damos clic en Thisworkbook (1).
- Seleccionamos el evento de Open (2), para que cada vez que abramos este archivo de Excel se ejecute el código que contiene; en este casi indicara el mensaje de alertas.
- Escribimos el código que se muestra en (3), el cual hará que podamos ver el mensaje de alerta al abrir el archivo.
CÓDIGO USADO EN EL EJEMPLO
menorcero = Range("D2").Value
mensaje1 = "Facturas vencidas :" & menorcero
igualcero = Range("E2").Value
mensaje2 = "Facturas vencidas hoy :" & igualcero
proximos = Range("F2").Value
mensaje3 = "Facturas Próximas a vencer :" & proximos
pendientes = Range("G2").Value
mensaje4 = "Facturas Pendientes :" & pendientes
MsgBox (mensaje1 & vbNewLine & mensaje2 & vbNewLine & mensaje3 & vbNewLine & mensaje4)
Así es como se mostrara el mensaje al abrir nuestro archivo de Excel y tener idea de cual es el estado para este caso de nuestras facturas.
📌 Descarga archivo trabajado
¿Quieres aprender a usar Excel?
¡Toma estos Cursos de Excel totalmente gratuitos!
Básico – Intermedio – Avanzado
👉 Haz clic en el botón naranja para empezar.
Buenos días; cuando descargo el archivo para revisar la formula del tutorial de las fechas de vencimiento, que digan pagadas no se visualiza nada, quisiera saber como se puede ver
Hola vi este tutorial, que me parece muy bueno. Pero no logro encontrar la condición para que las facturas digan «Pagadas»
Buen dia Miguel
Estuve vendo el video de Creacion de Alertas de vencimiento y me queda una consulta
Yo deseo que los vencimientos ya cumplidos se queden almacenados como historial y que se cree un nuevo alerta con igual frecuencia. Como se puede llevar a cabo
Es decir que cuando se cumple una tarea, la misma figure como realizada y vuelva a abrirse una fila con otra tarea igual a ser cumplida en igual frecuencia
Podria darme alguna ayuda
Tío tech necesito saber como agregar una nueva fila sin que se me borre la fórmula del capital multiplicado con el PORCIENTO que meda el valor automático lo cual se me borra cada ves que inserto una nueva fila o un nuevo registro de cliente
Buenas tardes, te felicito por tus videos me puedes apoyar con lo ultimo del tutorial cuando las facturas se encuentran ya pagadas, lo realice pero no me sale esa condición.
Buenas tardes Miguel (tío Tech), he descargado el archivo para revisar la formula del tutorial de las fechas de vencimiento que digan pagadas pero no logro visualizar nada, quisiera su apoyo por favor. Agradezco su atención.
[…] Descargar ejemplo: https://eltiotech.com/como-calcular-f… […]
Gracias, muchas gracias por compartir tu tiempo y conocimientos, es la mejor forma de ser persona. Un fuerte abrazo desde Colombia.
Excelente manera de explicar, felicitaciones y que Dios bendiga y multiplique tus conocimientos
hola buenas tardes la verdad que siempre veo tus videos ,porque me encata tu forma de explicar y me es de gran ayuda todos los que voy aprendiendo.Desde ya muchas gracias!!! por tu tiempo!!
Gracias por las enseñanzas compartidas, sigo cada video para actualizar conocimientos.
los videos son con un alto de contenidos muy bien explicados
Soy Carlos F. Torres, Una persona de edad que tiene un poco de aptitud para Excel y me gusta. Sus videos me gustan mucho sobre todo por la sencillez de presentación y amplitud que le dan a la información que dan. Ahí voy a mi paso. Muchas gracias por los diferentes aportes de los que me he beneficiado.
Te felicito sobre el tutorial esta muy claro, me salió todo perfecto, excepto la condición mas para cuando las facturas están pagadas no degastes comentarios gracias por todo
Tío
muchas gracias por tus tutoriales, tus aportaciones son excepcionales