Hi all,
I am new to the forum, so if this post is not supposed to be here, please redirect.
I have a list of records (as shown in the attached file). I am trying to determine the Duration of the account based on the start/end date of the contracts under the accounts.
The logic that I use is: the start date of the first contract is also the start date of the account. if start/end date of the 2nd contract is within the start/end date of the 1st contract, then do nothing. However, if end date of the 2nd contract is greater than that of the first contract, use the end date of the 2nd contract as the end date of the account. Continue to use this logic until all contracts are checked.
The final result of this will be:
For account A1, start date is: 1/1/2015, and end date is 12/31/2016
For account A2, start date is: 1/1/2015, and end date is 6/30/2016
I have tried using a Do-while loop within a Do-while loop, but unable to get the logic to work right. Please helps.
Much appreciated.
Attached files
Contract sample 2.xlsx