Recently I have been using more dynamic array formulas and have come across an unusual situation where a date is not treated like a date.
Remember dynamic arrays are currently only available in the subscription version of Excel.
Have a look at the image below.
You can download the example file at the button at the bottom of the post if you want to see it in practice.
Cell B1 is an input cell to set the first date of the sequence.
Cell E1 creates sequential numbers using the SEQUENCE function.
Row 2 has the dates and is formatted as a date.
Row 3 is the problem. If I try calculate the number of calendar days in the month using DAY and EOMONTH functions it gives a value error as if the entry in E3 is not a date.
If I multiply the E2# reference by 1 it fixes the issue as per row 4, but I shouldn’t have to do that.
I have had this is other cases where a number is not treated a number until you multiply it by 1 or perform a calculation with it.
Not sure if anyone else has seen this?
If you want to learn more about dynamic arrays I have a free Webinar Recording plus a pdf manual at the link below.
Free Introduction to Dynamic Arrays Course
Download Example file
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
In budgets, forecasts, financial models and even reporting models repeating the numbers 1 to 12 can be useful. The SEQUENCE and MOD functions can make it easy and scalable.
When you transform data using Power Query it will often automatically promote the headers (use first rows headers) and guess the data types for each column. You can stop it doing this.
You can use a keyboard shortcut to enter today’s date in a cell, but you can also use it in lots of other places in Excel.
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
I was recently working with some data that had some issues with the sign on the quantities. The quantities should have had the same sign as the associated dollar amount, but they didn’t. Here is how I fixed it.
Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.
Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.
If you need to confirm a number is a whole number you can use a function with a short name.
Some people like to use bullet points in text boxes. Here is a simple technique to insert bullet points based on a list of entries in an Excel sheet.
If you have the subscription version of Excel you may have the new LET function. This function lets you capture variables within a formula. Let’s see an example.
I learned something new recently about underlines. They are not all created equal. The Single Accounting underline has some advantages.
Macros are designed to reduce keystrokes and mouse clicks. Here is a perfect example. It takes seven separate mouse clicks to use an Outlook email template as a new email. Here is a macro that does it in one.
One of the most powerful statements in VBA is Selection. This holds whatever the user has selected just before they ran the macro. Normally it is a range, but what if it isn’t?
If you need to refer to the first cell (top, left cell) in a range there is an easy way to do it.
One of the frustrations with using array syntax is that you always have to type all the entries between the curly brackets. You couldn’t link to cells. Well that has all changed with dynamic arrays.
Let’s say that you need to create a list of files in a folder. This is possible using VBA, but it is also possible in Power Query and it’s much easier to do in Power Query.
Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post.
In a For Next loop you don’t have to include the variable in the Next statement. But ….