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

jk1nn1ck_*20973

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

 

Can someone please help me build out a formula for this?

 

Thanks,

Bi-Weekly_End_Date_List_49226.docx

PaulRitner

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