Dynamic Column selection based on date difference

Follow

Souradeep

• October 17, 2019 18:26

I have  table with two date columns (date1 and date2) and 100 other fields from P1 to P100.

 

Requirement is to have another sheet which will have the two dates and based on the date difference 5 columns out of the P1-P100.

For example, if the dates are 01MAR2018 and 01Jun2018 then the second sheet should contain P3-P7 as F1-F5 (3 is the diff between Jun and Mar 2018).

Similarly, if the two dates in a row are 01DEC2018 and 01DEC2019 then the second sheet will contain P12-P16 as F1-F5. The second sheet will have Date1, Date 2, Date Diff, F1, F2, F3, F4, F5 ie 8 columns.

0

• 

  Konsta Danyliuk

  • October 22, 2019 07:50

  Hello Souradeep.

  Lets first confirm that I properly got the requirement.

  • You have a SourceSheet with columns date1 date2 P1 P2 P3...P100.
  • You would like to calculate a difference between date1 and date2 in months.
  • Then you want to create a new Sheet for every SourceSheet record where you need to reference a) columns date1, date2, datediff (in months) and b) the appropriate number of P columns based on certain criteria.

  If all the above is correct, may I ask you to provide more details on the criteria for P columns reference, please.

  • Whether you need a number of P columns that corresponds to the month difference, e.g. for a 3-month difference, columns P1, P2, P3 should be referenced?
  • Or you need P columns that represent month order numbers for a certain date difference e.g. for date1:01/01/2018 and date2: 01/05/2018 the datediff will be 4 and columns P2(Feb), P3(Mar), P4(Apr) and P5(May) should be referenced?

  Perhaps you could create a source and a result sample (e.g. in Excel) and attach its screenshot?

  It would be also helpful, if you could provide some background on this use-case, what exactly you are trying to process. In this case, I hope, we could offer an alternative approach.

   

  0

  Comment actions Permalink

Please sign in to leave a comment.