I am trying to list dates in October, which is 1 to 31 days, and do this seven times. When I hit row 31, I want the list to start again at row 1. How can I do that?
I have an ‘Output’ sheet where I am trying to show my results and a ‘Daily Forecast’ sheet where the data sits. Let’s say I have 10/1/2021 in cell FP394 and it goes down to 10/31/2021 in cell FP424. How can I repeat the range of 10/1/2021 to 10/31/2021, seven times, stacking the next one under the prior one, all in one row? I tried Offset, Mod, and Row functions, but I couldn’t get it working. It never goes back to the very first cell (FP394) after the 31st cell (FF424). I would like a formula solution, not a VBA solution. Thanks!
>Solution :
Expanding on my comment, you can use mod() and row() to get what you are after:
=DATEVALUE("2022-10-" & 1+MOD(ROW()-22, 31))
That’s assuming that your dates start on row 394. Adjust accordingly if that changes.