Help with Excel formula

[anu2021]

Hi, Request to help with an excel formula for below scenario,

Data exisiting in the format , Basis the start date M1 will be considered and items count is given

Start Date	End Date	Month1	Month2	Month3	Month4	Month5	Month6	Month7	Month8	Month9	Month10	Month11	Month12
1-Jan-25	31-Dec-25	1	1	1	1	1	1	1	1	1	1	1	1
1-Apr-25	31-Aug-25	1	1	1	3	3
16-Jun-25	31-Jan-26	1	1	1	1	1	2	2	3

Desired Output - Basis the start the date header Month values to be derived and item count to be update in corresponding months

Start Date	End Date	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26
1-Jan-25	31-Dec-25	1	1	1	1	1	1	1	1	1	1	1	1
1-Apr-25	31-Aug-25				1	1	1	3	3
16-Jun-25	31-Jan-26						1	1	1	1	1	2	2	3

[anu2021]

Hi, Thank you for checking on the request.. I will seek help from the suggested microsoft excel community for any queries in future.. Looks like there is an error in formula and the request is to update Month and the corresponding values like shown in expected output.. the number of record is close to 3lakhs and start date will vary across years..!! 

[burakkaragoz]

Thanks for the update! Mentioning that you have 3 Lakhs (300,000) rows changes the approach completely.

Since I already gave you the formula logic in my previous post, let me explain why you are seeing an error and why you should actually stop using formulas for this specific task.

1. Why the Formula showed an "Error" The logic I shared works perfectly on standard data. The error is almost certainly due to Data Types in your column headers.

• The Problem: Your headers (e.g., Jan-25) are likely stored as Text.

• The Fix: Excel formulas like YEAR() and MONTH() fail on Text. You would need to convert those headers to actual Date formats (e.g., 01/01/2025) for the formula to recognize them.

2. CRITICAL WARNING: Performance ⚠️ Running a complex array formula (like LET or INDEX) across 300,000 rows will drastically slow down your workbook or potentially crash Excel. Excel's calculation engine isn't designed for this volume of matrix transformation via cell formulas.

The "Correct" Solution for Large Data (Power Query) Since you are dealing with 300k rows, the best way to do this in Excel is using Power Query (Get & Transform). It handles this volume easily without freezing your PC.

1. Select your data > Data Tab > From Table/Range.

2. Select columns Month1 to Month12 > Right Click > Unpivot Columns.

   • This instantly converts your wide 12 columns into tall rows.

3. Add a Custom Column to calculate the "Forecast Date" based on your Start Date + the Month Index.

4. Then use Pivot Column if you need to reshape it back.

I highly recommend switching to this method. It will be instant compared to waiting for 300,000 formulas to calculate!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[anu2021]

Thank you very much... i was able to get the expected result with the formula you have provided earlier as my sample was given for M1 to M12, whereaas my actual data has till M120... so with this update in the formula it is working 🙂 

[burakkaragoz]

You are very welcome @anu2021 !

I am glad to hear you were able to adapt the logic for 120 months. That is quite a wide dataset!

Just keep that Power Query tip in your back pocket. With 120 columns and 300,000 rows, Excel is performing millions of calculations instantly. If the workbook starts to feel slow or "heavy" in the future, unpivoting that M1-M120 range in Power Query will be the best way to speed it up.

Happy Excelling!

[burakkaragoz · Accepted solution]

Hi @anu2021 ,

Quick Note: I noticed you posted this in the Power BI Community, but specifically asked for an Excel formula. I have provided the Excel solution below to help you out. However, for future Excel-specific questions, you will get faster and more specialized answers in the Microsoft Excel Community.

Now, to your solution!

You are essentially trying to convert a "relative" timeline (Month 1, Month 2) into an "absolute" calendar timeline (Jan 25, Feb 25).

The Excel Solution We need a formula that calculates the month difference between your Header Date and your Start Date to pick the correct value.

Assuming your data layout:

• Start Date: Cell A2

• Month 1 to Month 12 Data: Columns C to N (C2:N2)

• Target Header (e.g., Jan-25): Cell P1 (Must be a date format)

Paste this into P2:

Excel

=LET(
    MonthIndex, (YEAR(P$1) - YEAR($A2)) * 12 + MONTH(P$1) - MONTH($A2) + 1,
    IF(AND(MonthIndex >= 1, MonthIndex <= 12), INDEX($C2:$N2, 1, MonthIndex), "")
)

Pro Tip for Power BI: If you ever need to do this transformation inside Power BI instead of Excel, you wouldn't use formulas. You would use Power Query to "Unpivot" your Month columns. That makes this kind of analysis instant without complex logic!

Here is a reference to the functions used:

• LET function (Microsoft Learn)

Hope this helps!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.