Forum Discussion

anu2021's avatar
anu2021
Frequent Visitor
1 year ago
Solved

Help with Excel formula

I am trying to do a date mapping in the following data basis the the value present fields...  Value > 0 , the first value month will start month and the last value >0 should be the end month.. Sample...
Need Help
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    Hi anu2021 

    Can you please try the below steps to get your requirement ?

    1. Use below formula to get Start Period

    =TEXT(INDEX($B$1:$O$1, MATCH(TRUE, INDEX(B2:O2>0, 0), 0)), "yy-mmm")

    2. Use below formula to get End Period
    =TEXT(INDEX($B$1:$O$1, MATCH(1, INDEX((B2:O2>0)*(COLUMN(B2:O2)=MAX(IF(B2:O2>0, COLUMN(B2:O2)))), 0), 0)), "yy-mmm")

     



    If this answers your questions, kindly accept it as a solution and give kudos.


    I have also attached the excel file.

mdaatifraza5556's avatar
mdaatifraza5556
Super User
1 year ago

Hi anu2021 

Can you please try the below steps to get your requirement ?

1. Use below formula to get Start Period

=TEXT(INDEX($B$1:$O$1, MATCH(TRUE, INDEX(B2:O2>0, 0), 0)), "yy-mmm")

2. Use below formula to get End Period
=TEXT(INDEX($B$1:$O$1, MATCH(1, INDEX((B2:O2>0)*(COLUMN(B2:O2)=MAX(IF(B2:O2>0, COLUMN(B2:O2)))), 0), 0)), "yy-mmm")

 



If this answers your questions, kindly accept it as a solution and give kudos.


I have also attached the excel file.

  • anu2021's avatar
    anu2021
    Frequent Visitor
    1 year ago

    This helps and solves the problem for 2 lakh+ records... Thank you 🙂