• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
Excel Version
  1. 365
As of November 26, 2019, the Excel team has announced that Dynamic Arrays have rolled out to 20% of Office 365 Subscribers on the Monthly channel. This means tens of millions of people are getting their first look at SORT, SORTBY, FILTER, UNIQUE, RANDARRAY, and SEQUENCE.

In my seminars, I always talk about SORT, FILTER and UNIQUE as being the rockstars of these new functions, painting SEQUENCE as being a lowly Clark Kent to the superhero functions. But, as I reveal later in the session, SEQUENCE is the lowly function that turns almost every function into an array function.

During one of my last sessions, held in Springfield Missouri, Lisa, a data analyst sitting in Row 3 of the seminar suggested a great use for SEQUENCE. She frequently has to build a report that shows the last 52 weeks, in descending sequence. Every row represents a Monday starting from this previous Monday and going backwards.

Since I am writing this on Friday, November 29, 2019, I am going to start by entering the most recent Monday in cell D1. With my US date settings, that means 11/25/2019 is in D1. Add a heading in A3.
1575024148302.png

The SEQUENCE function has one required argument and three optional arguments:
Rows
[Columns]
[Start]
[Step]

For this example Rows will be 52. Columns could be omitted or you could use 1. The Start value will be the date in D1. The Step value will be negative 7, so each date goes backwards one week.

Type =SEQUENCE(52,1,D1,-7) and press Enter.

This gets you the correct answer, but the wrong format:
1575024403508.png

Select A4:A55 (From A4, you can press Ctrl+Shift+DownArrow).

The first thing you might notice is that the Name Box is using the Array Formula Nomenclature of A4# where the Hashtag means the entire dynamic array currently generated by the formula in A4.
1575024529770.png

Apply a Long Date format and you have successfully generated 52 weeks in descending sequence:
1575116664091.png


While this solves Lisa's problem, do we really need the date to be entered in cell D4?

What is the real reason I entered the date in cell D4? It is because I, Bill Jelen, author of 60 books about Excel, don't know how to enter a date as an argument in another function. I know that I could use =SEQUENCE(52,1,DATE(2019,11,25),-7) but that seems like cheating.

I tried entering =SEQUENCE(52,1,11/25/2019,-7) but this failed because Excel tried to do the math of 11 divided by 25 and that quotient further divided by 2019. This is clearly not what I want.
1575116732943.png


I actually Googled for "how to use a date as an argument to another function" but none of the results were what I needed.

After a little experimenting in the Function Arguments dialog box, the solution that did work was to wrap the date in quotes. The Function Arguments showed that Excel converts "11/25/2019" to a serial number of 43,794. Normally, as Excellers, we hate numbers stored as text, but apparently this is one way to solve the problem.
1575116829727.png


Do you have a better solution to the problem of using dates as arguments in Excel functions? If you do, leave a comment below.
Author
MrExcel
Views
2,680
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from MrExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top