The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel dates are just numbers, SEQUENCE works well for generating dates. In this first worksheet, we have a couple cells set up to collect input for a start date, and days. I’ll enter the SEQUENCE function in cell E5 and configure it to use these inputs. The rows argument corresponds to the number of dates we want, so we want to use C5. For columns, we want 1. For start, we pick up the value in C4. Finally, for step, I’ll enter 1, since we want these dates to be consecutive. When I enter the formula, we get a list of 7 dates starting on May 1. If I adjust either the start date or days, the SEQUENCE function immediately returns a new list of dates. One thing I want to mention is that I’ve already formatted cells in column E as dates. However, if I apply general number formatting , you can see the large serial numbers that represent dates in Excel. If you see numbers like this when you expect to see dates, you need to apply date formatting. On the next worksheet, let’s use SEQUENCE to generate a list of all dates in a given month. We’ll do this with help from the “end of month” or EOMONTH function. C4 contains the start date. In C5, we’ll use the end of month function. This function takes two arguments, a start_date, which we get from C4, and months, which represents how many months we want to offset from the start date. In this case, because we want to stay in the same month, we use zero for months. In C6, we’ll calculate total days. This is the end date minus the start date, plus 1. Now in E5, I’ll enter the SEQUENCE function. We get rows from C6. Columns is 1 And we get start from C4. When I enter the formula, SEQUENCE returns the 31 dates in May. Notice I can change the start date to any month I like and the formula continues to work properly.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
