Help with Excel formula

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:

=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.