5 Replies Latest reply on Apr 16, 2018 1:14 AM by Tushar More

    Formula required for matching of 2 criteria (with range of dates)

    Gowri Shanmugam

      Hi there,


      I have 2 datasets (say dsA and dsB).

      dsA contains fields 1. Invoice start date 2. Invoice end date (both together is basically a range of dates) 3. Due date 4. Period type

      dsB contains fields 1. Invoice numbers 2. Invoice date 3. Payment type (which is technically the same data as Period Type in dsA with extra characters which can be ignored - refer note below)


      What I need is this:-

      I need to pull Due dates (from dsA) against Invoice numbers (in dsB) based on Invoice dates (in dsB) for each Period/Payment types (same field in both data sets)


      I have attached a sample dataset for clarity along with target data.


      Can someone please provide a formula to arrive at the 'DUE_DATE' for the field 'INVOICE_NUM'?


      Note:- For matching Period Type to Payment Type, we need to ignore the '_R' or '_I' in Payment date field as all other characters would be a match between the 2 datasets.


      Thanks a lot!