This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 31%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECV%3C/text%3E%3C/svg%3E)
I have a file originally saved on February 22,2024 where I used the STOCKHISTORY function to retrieves dates between 2018-2023. The date range is derived (not typed in directly but cell references) based on user input. If I change the Last Date February 22, 2024, the STOCKHISTORY function returns data, but all the dates between 12/28/2023 and 2/20/2024 are skipped. It does this for every Stock. If I delete the formula an re-enter it. It still skips the dates.
Oddly enough, if I open a fresh copy of Excel and enter the formula it doesn't skip the dates.
My only option appears to be to delete all STOCKHISTORY functions. SAVE AS a new name. Then open the new file and reenter all the STOCKHISTORY functions.
This happened on January 31, 2024 as well.
I can provide the file that exhibits this behavior. I would like to know how to prevent this from happening.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 13%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJH%3C/text%3E%3C/svg%3E)
Does this issue occur with other Excel formulas? Such as NETWORKDAYS or DAYS360.
If possible, you can share examples with us (For example, save the file as. xml file and upload it as an attachment.). But please be careful not to include important data!
Thx~
STOCKHISTORY_SKIPPING.xml
I don't know how you convert that back to Excel. Even if you do, I don't think it will exhibit the behavior I described. As I mentioned, I can open a new sheet and the dates will not be skipped. No trouble with other functions.
Hi @Chris Van Winkle
Thanks for your sharing.
Could you please tell us which version of Office are you using?
You issue can be reproduced on my environment with Microsoft 365 Apps installed. In my opinion, it is related to a multi-cell array formula.
According to the article 'Rules for changing array formulas', you can't add new cells to a block of result cells,
From your shared workbook, the result range is blocked which starts from F35 to K1639.
I suggest you select the range of cells from F35 to K1639 that contain the array formula, and delete it. Re-enter the formula, but do not press Ctrl + Shift +Enter, please press Enter directly to have a check.
Then the formula will be a dynamic array formula without curly braces {}.
In following image, the blue box in the lower right corner is the result cell range of the dynamic array.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
I have Microsoft 365, Excel Version 2404 (build 17328.20068). I don't have curly backets {} around the formula and I did not press Ctrl + Shift +Enter when I created it. I don't see how I can tell that the "SPILLWAY" is being limited to the F35:K1639 range. If that is the case, I find it odd that I wouldn't get a #SPILL error as opposed to it skipping data. Perhaps it is some remnant from an earlier version of this spreadsheet or Excel. Thanks for looking into it.
Hi @Chris Van Winkle
Sorry for the delay.
It's a bit strange indeed that this formula appears as an array formula in my test environment.
As you said, perhaps it is some remnant from an earlier version of this spreadsheet. My suggestion is to clear the cells where this array formula is located, and then re-enter the dynamic array formula.