This blog post from our Data to Decisions series aims at creating custom date sequences with skips—specifically one where we generate every Nth day of a week from a given start date. With the powerful SEQUENCE function, this is just a breeze, read along to know how!
Given a starting date in cell D2 as 6th Jan, 2023, we’ll generate every 3rd day as given in D4.
The 365 in cell D3 is the number of rows of custom dates to generate.
To get this we use the SEQUENCE function, the syntax for which is:
This function takes as input, the rows to create, columns to create, the starting point, and the steps to increment each time (which is the input here in cell D4)
Our formula for cell D6 would be,
=SEQUENCE(D3, 1, D2, D4)This returns a series of 365 dates starting from 06-01-2023 and generates every 3rd day.
To understand this clearly, use the TEXT function here to return the day of the week in cell E6.
=TEXT(D6, “ddd”)The TEXT function here, returns the date as a day format.
This gives 365 dates with custom skips i.e. every 3rd day from our starting date as shown below
Check our step-by-step explainer video to Generate Nth Day in Excel under various scenarios here:
Read our pots on generating only a specific weekday and how to generate only weekdays (Mon-Fri) in Excel.
If you have any feedback or suggestions, please post them in the comments below.
