Forum Discussion
Help with Excel formula
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 |
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:
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.
5 Replies
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:
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.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..!!
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.
Select your data > Data Tab > From Table/Range.
Select columns Month1 to Month12 > Right Click > Unpivot Columns.
This instantly converts your wide 12 columns into tall rows.
Add a Custom Column to calculate the "Forecast Date" based on your Start Date + the Month Index.
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.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 🙂
