In this data set there are 3 separate policies that are either have 6 month or 12 month terms. I want to define each policy as 'NEW' for the first 12 months - i.e. For ID '01' in E2:E4 it is labeled as 'New' only once and I need a formula to turn it into F2:F4. I only need to change New/Renew for 6 month policies, not 12 month.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Rename values based on multiple criteria
- Thread starter qhont
- Start date
-
- Tags
- #excel #formulahelp
ExceLoki
Well-known Member
- Joined
- Dec 13, 2021
- Messages
- 542
- Office Version
- 365
- Platform
- Windows
try this
-------------
-------------
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | ID | start date | end date | term | formula | new/renew | expected | ||
| 2 | 1 | 1/1/2021 | 6/1/2021 | 6 mo | new | new | new | ||
| 3 | 1 | 6/3/2021 | 1/3/2022 | 6 mo | new | renewal | new | ||
| 4 | 1 | 1/3/2022 | 6/3/2022 | 6 mo | renewal | renewal | renewal | ||
| 5 | 2 | 2/1/2021 | 2/1/2022 | 12 mo | new | new | new | ||
| 6 | 2 | 2/4/2022 | 2/4/2023 | 12 mo | renewal | renewal | renewal | ||
| 7 | 3 | 1/1/2021 | 6/1/2021 | 6 mo | new | new | new | ||
| 8 | 3 | 6/3/2021 | 1/3/2022 | 6 mo | new | renewal | new | ||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E8 | E2 | =IFS(D2="6 mo",IF(COUNTIF($A$2:$A$8,A2)>2,IF(C2-MINIFS($B$2:$B$8,$A$2:$A$8,A2)>367,"renewal","new"),"new"),D2="12 mo",IF(COUNTIF($A$2:$A$8,A2)>=2,IF(C2-MINIFS($B$2:$B$8,$A$2:$A$8,A2)>365,"renewal","new"),"new")) |
Upvote
0
Solution
Thanks so much ExceLoki!
Upvote
0
Similar threads
- Solved
