El tutorial muestra cómo puede aprovechar la nueva función SEQUENCE para generar rápidamente una lista de fechas en Excel y usar la función Autocompletar para llenar una columna con fechas, días hábiles, meses o años.
Hasta hace poco, solo había una manera fácil de generar fechas en Excel: la función Autocompletar. La introducción de la nueva función de matriz dinámica SEQUENCE también ha hecho posible crear una serie de fechas con una fórmula. Este tutorial analiza en profundidad ambos métodos para que pueda elegir el que mejor se adapte a sus necesidades.
Cómo llenar series de fechas en Excel
Cuando necesite llenar una columna con fechas en Excel, la forma más rápida es usar la función Autocompletar.
Autocompletar una serie de fechas en Excel
Rellenar una columna o fila con fechas que se incrementan en un día es muy fácil:
- Escriba la fecha inicial en la primera celda.
- Seleccione la celda con la fecha inicial y arrastre el controlador de relleno (un pequeño cuadrado verde en la esquina inferior derecha) hacia abajo o hacia la derecha.
Excel generará inmediatamente una serie de fechas en el mismo formato que la primera fecha que ingresó manualmente.
Rellene una columna con días de la semana, meses o años
Para crear una serie de días laborables, meses o años, realice una de las siguientes acciones:
- Llene una columna con fechas secuenciales como se describe arriba. Después de eso, haga clic en el botón Opciones de Autocompletar y elija la opción deseada, diga Llenar meses:
- O puede ingresar su primera cita, hacer clic con el botón derecho en el controlador de relleno, mantener presionado y arrastrar tantas celdas como sea necesario. Cuando suelte el botón del mouse, aparecerá un menú contextual que le permitirá seleccionar la opción necesaria, Completar años en nuestro caso:
Llenar una serie de fechas incrementando por N días
Para generar automáticamente una serie de días, días de la semana, meses o años con un paso específicoEsto es lo que necesitas hacer:
- Introduzca la fecha inicial en la primera celda.
- Seleccione esa celda, haga clic con el botón derecho en el controlador de relleno, arrástrelo a través de tantas celdas como sea necesario y luego suéltelo.
- En el menú emergente, seleccione Serie (el último elemento).
- En el cuadro de diálogo Serie, seleccione la unidad de Fecha de interés y establezca el valor de Paso.
- Haga clic en Aceptar.
Cómo hacer una secuencia de fechas en Excel con una fórmula
En uno de los tutoriales anteriores, vimos cómo usar la nueva función de matriz dinámica SEQUENCE para generar una secuencia numérica. Debido a que internamente en Excel las fechas se almacenan como números de serie, la función también puede producir fácilmente una serie de fechas. Todo lo que tiene que hacer es configurar correctamente los argumentos como se explica en los siguientes ejemplos.
Nota. Todas las fórmulas discutidas aquí solo funcionan en las últimas versiones de Excel 365 que admiten matrices dinámicas. En Excel 2019, Excel 2016 y Excel 2013 predinámico, utilice la función Autocompletar como se muestra en la primera parte de este tutorial.
Crear una serie de fechas en Excel
Para generar una secuencia de fechas en Excel, configure los siguientes argumentos de la función SECUENCIA:
SECUENCIA(filas, [columns], [start], [step])
- Filas: el número de filas para llenar con fechas.
- Columnas: el número de columnas para llenar con fechas.
- Inicio: la fecha de inicio en el formato que Excel puede entender, como «8/1/2020» o «1 de agosto de 2020». Para evitar errores, puede proporcionar la fecha utilizando la función FECHA como FECHA (2020, 8, 1).
- Paso: el incremento para cada fecha subsiguiente en una secuencia.
Por ejemplo, para hacer una lista de 10 fechas comenzando con el 1 de agosto de 2020 y aumentando 1 día, la fórmula es:
=SEQUENCE(10, 1, "8/1/2020", 1)
o
=SEQUENCE(10, 1, DATE(2020, 8, 1), 1)
Alternativamente, puede ingresar el número de fechas (B1), la fecha de inicio (B2) y el paso (B3) en celdas predefinidas y hacer referencia a esas celdas en su fórmula. Dado que estamos generando una lista, el número de columnas (1) está codificado:
=SEQUENCE(B1, 1, B2, B3)
Escriba la siguiente fórmula en la celda superior (A6 en nuestro caso), presione la tecla Intro y los resultados se distribuirán automáticamente en el número especificado de filas y columnas.
Nota. Con el formato General predeterminado, los resultados aparecerán como números de serie. Para que se muestren correctamente, asegúrese de aplicar el formato de fecha a todas las celdas del rango de derrame.
Hacer una serie de días laborables en Excel
Para obtener solo una serie de días hábiles, envuelva SEQUENCE en la función WORKDAY o WORKDAY.INTL de esta manera:
DIA LABORAL(start_date -1, SEQUENCE(no_of_days))
Como la función WORKDAY suma el número de días especificado en el segundo argumento a la fecha de inicio, le restamos 1 para incluir la fecha de inicio en los resultados.
Por ejemplo, para generar una secuencia de días laborables a partir de la fecha en B2, la fórmula es:
=WORKDAY(B2-1, SEQUENCE(B1))
Donde B1 es el tamaño de la secuencia.
Consejos y notas:
- Si una fecha de inicio es sábado o domingo, la serie comenzará el siguiente día hábil.
- La función DÍA LABORAL de Excel asume que el sábado y el domingo son fines de semana. Para configurar fines de semana y días festivos personalizados, utilice la función WORKDAY.INTL en su lugar.
Generar una secuencia de meses en Excel
Para crear una serie de fechas incrementadas en un mes, puede usar esta fórmula genérica:
FECHA(año, SECUENCIA(12), día)
En este caso, coloca el año objetivo en el primer argumento y el día en el tercer argumento. Para el segundo argumento, la función SECUENCIA devuelve números secuenciales del 1 al 12. Según los parámetros anteriores, la función FECHA produce una serie de fechas como se muestra en la parte izquierda de la siguiente captura de pantalla:
=DATE(2020, SEQUENCE(12), 1)
Para mostrar solo el nombres de mesesconfigure uno de los siguientes formatos de fecha personalizados para el rango de derrame:
- mmm – forma corta como enero, febrero, marzo, etc.
- mmmm – forma completa como enero, febrero, marzo, etc.
Como resultado, solo aparecerán los nombres de los meses en las celdas, pero los valores subyacentes seguirán siendo fechas completas. En ambas series en la captura de pantalla a continuación, observe la alineación a la derecha predeterminada típica para números y fechas en Excel:
Para generar una secuencia de fechas que se incremente en un mes y comienza con una fecha específicause la función SEQUENCE junto con EDATE:
EDATE(start_date, SEQUENCE(12, 1, 0))
La función EDATE devuelve una fecha que es el número especificado de meses antes o después de la fecha de inicio. Y la función SEQUENCE produce una matriz de 12 números (o tantos como usted especifique) para forzar a EDATE a avanzar en incrementos de un mes. Tenga en cuenta que el argumento de inicio se establece en 0, por lo que la fecha de inicio se incluye en los resultados.
Con la fecha de inicio en B1, la fórmula toma esta forma:
=EDATE(B1, SEQUENCE(12, 1, 0))
Nota. Después de completar una fórmula, recuerde aplicar un formato de fecha adecuado a los resultados para que se muestren correctamente.
Crear una secuencia de años en Excel
Para hacer una serie de fechas incrementadas por año, use esta fórmula genérica:
FECHA(SECUENCIA(n, 1, AÑO(fecha_inicio)), MES(fecha_inicio), DÍA(fecha_inicio))
Donde n es el número de fechas que desea generar.
En este caso, la función FECHA(año, mes, día) construye una fecha de esta manera:
- El año lo devuelve la función SEQUENCE que está configurada para generar una matriz de números de n filas por 1 columna, comenzando en el valor del año desde start_date.
- Los valores de mes y día se extraen directamente de la fecha de inicio.
Por ejemplo, si ingresa la fecha de inicio en B1, la siguiente fórmula generará una serie de 10 fechas en incrementos de un año:
=DATE(SEQUENCE(10, 1, YEAR(B1)), MONTH(B1), DAY(B1))
Después de ser formateados como fechas, los resultados se verán de la siguiente manera:
Generar una secuencia de tiempos en Excel
Debido a que las horas se almacenan en Excel como números decimales que representan una fracción del día, la función SECUENCIA puede trabajar con horas directamente.
Suponiendo que la hora de inicio está en B1, puede usar una de las siguientes fórmulas para producir una serie de 10 veces. La diferencia está solo en el argumento del paso. Como hay 24 horas en un día, use 1/24 para incrementar en una hora, 1/48 para incrementar en 30 minutos, y así sucesivamente.
30 minutos de diferencia:
=SEQUENCE(10, 1, B1, 1/48)
1 hora de diferencia:
=SEQUENCE(10, 1, B1, 1/24)
2 horas de diferencia:
=SEQUENCE(10, 1, B1, 1/12)
La siguiente captura de pantalla muestra los resultados:
Si no quiere molestarse en calcular el paso manualmente, puede definirlo usando la función TIEMPO:
SECUENCIA(filas, columnas, inicio, HORA(hora, minuto, segundo))
Para este ejemplo, ingresaremos todas las variables en celdas separadas como se muestra en la captura de pantalla a continuación. Y luego, puede usar la siguiente fórmula para generar una serie de tiempo con cualquier tamaño de paso de incremento que especifique en las celdas E2 (horas), E3 (minutos) y E4 (segundos):
=SEQUENCE(B2, B3, B4, TIME(E2, E3, E4))
Cómo crear un calendario mensual en Excel
En este último ejemplo, usaremos la función SEQUENCE junto con DATEVALUE y WEEKDAY para crear un calendario mensual que se actualizará automáticamente según el año y el mes que especifique.
La fórmula en A5 es la siguiente:
=SEQUENCE(6, 7, DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1, 1)
Cómo funciona esta fórmula:
Utiliza la función SECUENCIA para generar una matriz de fechas de 6 filas (la cantidad máxima posible de semanas en un mes) por 7 columnas (la cantidad de días en una semana) incrementada en 1 día. Por lo tanto, los argumentos de filas, columnas y pasos no plantean preguntas.
La parte más complicada en el argumento de inicio. No podemos comenzar nuestro calendario con el primer día del mes objetivo porque no sabemos qué día de la semana es. Entonces, usamos la siguiente fórmula para encontrar el primer domingo antes del primer día del mes y año especificados:
DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1
La primera función FECHAVALO devuelve un número de serie que, en el sistema interno de Excel, representa el primer día del mes en B2 y el año en B1. En nuestro caso, es 44044 correspondiente al 1 de agosto de 2020. En este punto, tenemos:
44044 - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1
La función WEEKDAY devuelve el día de la semana correspondiente al primer día del mes de destino como un número del 1 (domingo) al 7 (sábado). En nuestro caso, son las 7 porque el 1 de agosto de 2020 es sábado. Y nuestra fórmula se reduce a:
44044 - 7 + 1
44044 – 7 es 4403, que corresponde al sábado 25 de julio de 2020. Como necesitamos el domingo, agregamos la corrección +1.
De esta manera, obtenemos una fórmula simple que genera una serie de números de serie que comienzan con 4404:
=SEQUENCE(6, 7, 4404, 1)
Formatee los resultados como fechas y obtendrá un calendario que se muestra en la captura de pantalla anterior. Por ejemplo, puede utilizar uno de los siguientes formatos de fecha:
- d-mmm-yy para mostrar fechas como 1-ago-20
- mmm d para mostrar el mes y el día como el 20 de agosto
- d para mostrar solo el día
Espera, pero nuestro objetivo es crear un calendario mensual. ¿Por qué aparecen algunas fechas del mes anterior y del siguiente? Para ocultar esas fechas irrelevantes, configure una regla de formato condicional con la siguiente fórmula y aplique el fuente blanca color:
=MONTH(A5)<>MONTH(DATEVALUE($B$2 & "1"))
Donde A5 es la celda más a la izquierda de su calendario y B2 es el mes objetivo.
Para conocer los pasos detallados, consulte Cómo crear una regla de formato condicional basada en fórmulas en Excel.
Así es como puedes generar una secuencia de fechas en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
Cuaderno de ejercicios para descargar
(archivo .xlsx)