Contributor

## If date falls within a date range in a list, then apply the end date of the date from the list

Hi,

I have the following:

• Module with a column for Lease Ending Date - My particular date I'm using for this example is 7/14/19

• List built that houses bi-weekly ending dates the Lease Ending Date can fall into (Goes from 2/3/19 to 2/17/19 to 3/3/19, etc.) - The date range the Lease Ending Date falls into is 7/8/19 - 7/21/19. (Image of List attached)

In a new column, I want a formula that will look at the Lease Ending Date, determine which date range the Lease Ending Date falls into within the list and apply the end date, 7/21/19 in this case, as the result

Thanks,

3 REPLIES 3
Highlighted
Certified Master Anaplanner

## Re: If date falls within a date range in a list, then apply the end date of the date from the list

Hi,

The easiest way I've accomplished tasks like this it to create a child list (to your Bi-Weekly End Dates) and list out the children dates that should be included... then to a lookup to fetch the parent.

For example

List 1  item :  3/3/19

Child list 2 items as children of 3/3/19

2/18/19

2/19/19

2/20/19

2/21/19

2/22/19

2/23/19

2/24/19

2/25/19

2/26/19

2/27/19

2/28/19

3/1/19

3/2/19

3/3/19

Do the FINDITEM on  list 2, then lookup the parent using the FINDITEM result.

Cheers,

Paul

Highlighted
Master Anaplanner/Community Boss

## Re: If date falls within a date range in a list, then apply the end date of the date from the list

Hi,

Try this solution,

Step 1: Add Start Date and End date of the week as shown below

Lease Date Mod with Country Dimension and line item - Lease Date and Biweekly Date

Step 2: Create a module with Country, Biweekly Date List as dimensions and line item - Date.

Line item, Date is Biweekly List Format and Summary is first non blank and apply the logic as shown.

Logic: IF Lease Date Mod.Lease Date >= Biweekly End Date.Week Start Date AND Lease Date Mod.Lease Date <= Biweekly End Date.Week End Date THEN ITEM(Biweekly End Date) ELSE BLANK

Step 3: Refer this Date line item in Lease Date module

Thanks,

Vignesh M

Highlighted
Frequent Contributor

## Re: If date falls within a date range in a list, then apply the end date of the date from the list

What you are faced with here is a mapping problem - how can Anaplan map the 'Lease ending date,' to the 'Bi-weekly ending dates.'

Mappings can be established either within list properties or a module. However, as the mapping is from a one to many we can not use list properties as we will not be able to add a range of dates to each 'Bi-weekly ending date,' in a list property.

Therefore, I would recommend building a new module dimensioned by the Anaplan 'Time' dimension and containing one line item which has been formatted as the list, 'Bi-weekly ending date,' and name this line item as per the list name, 'Bi-weekly ending date.'

Then for each date in the time dimension allocate it a, 'Bi-weekly ending date,' list item.

I would recommend that you build this in excel and import via a csv as this may be time consuming.

Now you are ready to build the formula which will reference the mapping module and pull the relevant Bi-weekly ending date. As long as your target module contains a time dimension all you need to do is reference the 'Bi-weekly ending date,' in the source ( the mapping module ) to pull through the relevant bi-weekly ending date.

Even if you add additional dimensions to your target this should no impact on how the b-weekly ending date pulls through to your target. Just ensure that the line item in your target is formatted to DATE.

Hope this helps,

Chris