Hi, I have two sets of input data. The first data set has a column with settlement dates as shown in the first grab below. The second is assigning date ranges to specified periods as shown in the second grab below. I am trying to run a formula so that if the STTL_DATE is >= Period Begin and <= Period End that it pulls into a new column with the corresponding Period field from the second set. Most solutions I am seeing call for a join tool but I cannot get this to work as there are no fields that join since I am looking for between a set of values. I'm still learning and hope to give back once I do, thank so much!
STTL_DATE |
10/30/2020 |
12/15/2020 |
12/20/2020 |
09/01/2020 |
Period | Period Begin | Period End |
P7 2020 | 8/1/2020 | 8/28/2020 |
P8 2020 | 8/29/2020 | 9/25/2020 |
P9 2020 | 9/26/2020 | 10/30/2020 |
P11 2020 | 11/28/2021 | 12/25/2021 |
P10 2020 | 10/31/2020 | 11/27/2020 |
P11 2020 | 11/28/2020 | 12/25/2020 |
Solved! Go to Solution.
You can use a generate rows tool to create a record for each day in the period, and then join that up with the dates in your first table to figure out what period they are in. See attached example.
Thank you! This looks to have worked very well. I appreciate your help