I'm trying to figure out a formula to highlight clients who are pulling more than 2 months ahead on their contracts (the contracts fall off of the query once a delivery order has been issued, so formula only pulling from undelivered contracts).
All of the contracts have a format of S-12345-1, and if client contracts are spread over >1 month (which most are) they would have following: S-12345-1, S-12345-2, S-12345-3, etc, with one dash per month.
I've got the formula down to where I can highlight contracts that are > 2 months ahead of the 1st day of the next month if the ending <> "-1", but I'm running into a challenge where I need the formula to only flag as AHEAD if the lowest remaining dash on a contract is >2 months ahead. The mini spreadsheet will more clearly expain hopefully!
Formula in column D is: =IF(AND(RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3))<>"1", EDATE([@[SHIPMENT_START]],-1)>DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)), "AHEAD", "CURRENT")
All of the contracts have a format of S-12345-1, and if client contracts are spread over >1 month (which most are) they would have following: S-12345-1, S-12345-2, S-12345-3, etc, with one dash per month.
I've got the formula down to where I can highlight contracts that are > 2 months ahead of the 1st day of the next month if the ending <> "-1", but I'm running into a challenge where I need the formula to only flag as AHEAD if the lowest remaining dash on a contract is >2 months ahead. The mini spreadsheet will more clearly expain hopefully!
Formula in column D is: =IF(AND(RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3))<>"1", EDATE([@[SHIPMENT_START]],-1)>DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)), "AHEAD", "CURRENT")
mrexcelquestion.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | SHIPMENT_START | 1ST DAY FOLLOWING MONTH | SELLER_REF_NO | CURRENT/AHEAD FORMULA | SHOULD READ | ||
2 | 2/1/2021 | 3/1/2021 | S-12345-1 | CURRENT | CURRENT | ||
3 | 3/1/2021 | 3/1/2021 | S-12345-2 | CURRENT | CURRENT | ||
4 | 4/1/2021 | 3/1/2021 | S-12345-3 | CURRENT | CURRENT | ||
5 | 5/1/2021 | 3/1/2021 | S-12345-4 | AHEAD | CURRENT | ||
6 | 6/1/2021 | 3/1/2021 | S-12345-5 | AHEAD | CURRENT | ||
7 | 7/1/2021 | 3/1/2021 | S-12345-6 | AHEAD | CURRENT | ||
8 | |||||||
9 | SHIPMENT_START | 1ST DAY FOLLOWING MONTH | SELLER_REF_NO | CURRENT/AHEAD FORMULA | SHOULD READ (current formula correct) | ||
10 | 5/1/2021 | 3/1/2021 | S-12345-2 | AHEAD | AHEAD | ||
11 | 6/1/2021 | 3/1/2021 | S-12345-3 | AHEAD | AHEAD | ||
12 | 7/1/2021 | 3/1/2021 | S-12345-4 | AHEAD | AHEAD | ||
13 | |||||||
14 | SHIPMENT_START | 1ST DAY FOLLOWING MONTH | SELLER_REF_NO | CURRENT/AHEAD FORMULA | SHOULD READ | ||
15 | 5/1/2021 | 3/1/2021 | S-12345-1 | CURRENT | CURRENT | ||
16 | 6/1/2021 | 3/1/2021 | S-12345-2 | AHEAD | CURRENT | ||
17 | 7/1/2021 | 3/1/2021 | S-12345-3 | AHEAD | CURRENT | ||
Sheet5 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D7,D15:D17,D10:D12 | D2 | =IF(AND(RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3))<>"1", EDATE([@[SHIPMENT_START]],-1)>DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)), "AHEAD", "CURRENT") |