If date falls within a date range in a list, then apply the end date of the date from the list
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
Can someone please help me build out a formula for this?
Re: If date falls within a date range in a list, then apply the end date of the date from the list
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.
List 1 item : 3/3/19
Child list 2 items as children of 3/3/19
Do the FINDITEM on list 2, then lookup the parent using the FINDITEM result.
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 Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA