The Excel SEQUENCE function is one of the new dynamic array functions introduced in the July 2020 semi-annual Excel 365 update.
This lesson showcases the power of dynamic arrays as you build a perpetual calendar in a few moments using the new SEQUENCE dynamic array function.
Dynamic arrays change best-practice for a huge number of common Excel tasks and I’m really excited about this huge leap forward for Excel. So much so that I added a new 24-lesson session Working with Dynamic Arrays session to the July 2020 fourth edition of my Expert Skills book. The new session seeks to empower Excel users with a complete mastery of dynamic arrays.
This article is an unedited lesson from one of the 24 short focused lessons in the new Dynamic Arrays session.
Leave me a comment if you find the article useful.
Lesson 4-16: Create a perpetual calendar using the Excel SEQUENCE function
1. Open the sample file Calendar-1 in Excel.
The user interface allows a user to select a year and month. You created year and month data for the drop-down lists using the SEQUENCE function in: Lesson 4‑15: Use an Excel SEQUENCE function.
2. Add an Excel SEQUENCE function to cell B8 to populate the calendar with date serial numbers that relate to the chosen Year and Month.
- Click in cell B8.
- Click: Formulas->Function Library->Math & Trig->Sequence.
The Excel sequence function’s Function Arguments dialog appears:
Clearly you need to populate five rows and seven columns with dates to complete the calendar.
- Type: 5 into the Rows box.
- Type: 7 into the Columns box.
You now need to consider the starting value.
In: Lesson 3 7: Understand date serial numbers, you learned that every date in Excel is represented by a number.
The Start argument (the number that needs to be returned to cell B8) needs to be the serial number for the first day in the month defined in the Year and Month drop-down lists (in cells C4 and C5).
In: Lesson 3 11: Use the DATE function to offset days, months and years, you learned about the DATE function. The DATE function accepts a Year, Month and Day argument (all of which must be numbers) and returns a date serial number.
It should be clear that the formula:
=DATE(C4, Data!B3, 1)
… will return the correct date serial number for cell B8 (the number that starts the sequence).
- Type : DATE(C4, Data!B3, 1)
… into the Start box. The Insert Function dialog should now look like this:
You can see that the first date serial number returned (in this example) is 44013. That’s because I had selected Jul for the month and 2020 for the year.
44013 is the date serial number for 1st July 2020.
- Click the OK button.
The calendar is filled with date serial numbers:
3. Format the date serial numbers to display as days.
Because the calendar only needs to show day numbers it is necessary to format the date serial numbers to display as days.
- Select cells B8:H12.
- Right-click on any selected cell.
- Click Format Cells… from the shortcut menu.
- Click Custom in the left-hand Category list.
- Type the format character: d into the Type box.
This custom format code formats a date as a single number (with no leading zeroes) representing the day.
- Click the OK button.
Day numbers are now shown in the body of the calendar.
4. Add the correct days of the week to cells B7:H7.
- Type this function into Cell B7:
This function takes the date serial numbers found in cells B8:H8 and formats them as three-letter day names.
5. Save your work as Calendar-2.
Here’s the sample file at the end of the session. If you ran into any problems you can study the finished calendar to identify the problem.
This lesson is excerpted from the above book.
This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.
It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Lessons in Session 4
|Session Four: Working with Dynamic Arrays|
|Lesson 4‑1: Understand one-dimensional arrays|
|Lesson 4‑2: Understand two-dimensional arrays|
|Lesson 4‑3: Create a legacy CSE array formula|
|Lesson 4‑4: Understand dynamic array formulas|
|Lesson 4‑5: Understand dynamic array compatibility|
|Lesson 4‑6: Create a drop-down list using a UNIQUE function|
|Lesson 4‑7: Use a FILTER function|
|Lesson 4‑8: Create a searchable drop-down list|
|Lesson 4‑9: Use a SORT function|
|Lesson 4‑10: Use a SORTBY function|
|Lesson 4‑11: Create two linked drop-down lists using dynamic arrays|
|Lesson 4‑12: Use a FILTER function|
with a custom logic array
|Lesson 4‑13: Construct Boolean AND/OR logic arrays|
|Lesson 4‑14: Use a FILTER function with AND/OR custom logic arrays|
|Lesson 4‑15: Use a SEQUENCE function|
|Lesson 4‑16: Create a perpetual calendar using the SEQUENCE function|
|Lesson 4‑17: Use a RANDARRAY function|
|Lesson 4‑18: Understand XLOOKUP (part|
|Lesson 4‑19: Understand XLOOKUP (part|
|Lesson 4‑20: Use XLOOKUP to perform a two-dimensional lookup|
|Lesson 4‑21: Use an XMATCH function|
|Lesson 4‑22: Use the SUMPRODUCT function|
|Lesson 4‑23: Use the SUMPRODUCT function with a logic array|
|Lesson 4‑24: Use a SORTBY function with a custom array|
|Session 4: Exercise|
|Session 4: Exercise Answers|
In this lesson you’ll create a fully functional calendar in Excel. The calendar showcases the new SEQUENCE dynamic array formula.
This article shows you how to calculate the number of working days between two dates in Excel using the NETWORKDAYS function.
This article shows how to calculate loan payment dates in Excel, using a formula that can calculate loans of any length and number of periods.
This article will show you how to create an Excel formula that calculates the number of days in the month for any given date.
This article shows you how to create an Excel formula to get the week of month from a date, as well as how to get the week of year from a date.