Assistance with complex formula using if, find, edate, and other functions

S

sheller

New Member
Joined
Dec 12, 2016
Messages
22
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")

mrexcelquestion.xlsx
ABCDE
1SHIPMENT_START1ST DAY FOLLOWING MONTHSELLER_REF_NOCURRENT/AHEAD FORMULASHOULD READ
22/1/20213/1/2021S-12345-1CURRENTCURRENT
33/1/20213/1/2021S-12345-2CURRENTCURRENT
44/1/20213/1/2021S-12345-3CURRENTCURRENT
55/1/20213/1/2021S-12345-4AHEADCURRENT
66/1/20213/1/2021S-12345-5AHEADCURRENT
77/1/20213/1/2021S-12345-6AHEADCURRENT
8
9SHIPMENT_START1ST DAY FOLLOWING MONTHSELLER_REF_NOCURRENT/AHEAD FORMULASHOULD READ (current formula correct)
105/1/20213/1/2021S-12345-2AHEADAHEAD
116/1/20213/1/2021S-12345-3AHEADAHEAD
127/1/20213/1/2021S-12345-4AHEADAHEAD
13
14SHIPMENT_START1ST DAY FOLLOWING MONTHSELLER_REF_NOCURRENT/AHEAD FORMULASHOULD READ
155/1/20213/1/2021S-12345-1CURRENTCURRENT
166/1/20213/1/2021S-12345-2AHEADCURRENT
177/1/20213/1/2021S-12345-3AHEADCURRENT
Sheet5
Cell Formulas
RangeFormula
D2:D7,D15:D17,D10:D12D2=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")
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi Sheller,

If that last digit of the SELLER_REF_NO is the delivery month offset then don't you need to add that to the date you're comparing against?

=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+RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3)),1)), "AHEAD", "CURRENT")
 
Upvote 0
Toadstool said:
=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+RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3)),1)), "AHEAD", "CURRENT")
Click to expand...
Thank you for the suggestion. Your suggested formula fixed the first example (see E2:E7), but didn't fix third example (E15:17). In that example I would need E16:17 to read as "current' because client has not yet ordered contract for 5/1/2021 delivery, so any subsequent delivery would be current. If client had already pulled May delivery and we were still in month of February, then should read AHEAD, if that makes sense?
 

Attachments

  • example.png
    example.png
    83.4 KB · Views: 6
Upvote 0
Sorry but I'm not following the difference between your example 2 and example 3.
 
Upvote 0
Toadstool said:
Sorry but I'm not following the difference between your example 2 and example 3.
Click to expand...
Apologies for being unclear. SHIPMENT_START is the original contracted month, but clients can 'pull ahead' or 'pull behind'. When a client orders from a contract and the contract has been issued a delivery order, that line falls off of this report. To catch clients who are pulling far ahead on their order (I set as =>2 months from date in column B), I wanted report to flag so sales could know to be in contact with client and book more product. In example 2, client is pulling two months ahead (has already pulled their April delivery contract and next SHIPMENT_START is =>2 MONTHS from column B.). In example 3 because the contract starts with a -1 (beginning of the contract) it should be marked as CURRENT and not as AHEAD because they have not taken delivery on anything on the contract. Because -1 has not been taken (CURRENT) then all subsequent dashes would also be considered CURRENT.
 
Upvote 0
Sorry but I'm just not following the subtle difference. It sounds like maybe the result may depend on a previous row result, but your starting formula contained no MATCH or other search function?

No need to apologize. It may just be I'm not smart enough to follow. :(

Maybe another forum member can help?
 
Upvote 0
Toadstool said:
Sorry but I'm just not following the subtle difference. It sounds like maybe the result may depend on a previous row result, but your starting formula contained no MATCH or other search function?

No need to apologize. It may just be I'm not smart enough to follow. :(

Maybe another forum member can help?
Click to expand...
Thank you anyway! Yes, I likely need to figure out how to add a MATCH function in the formula, just not clever enough to figure out how to do so. Thanks for trying in your responses above in any case!
 
Upvote 0
I need to let this go....

I was also confused by the 3 tables in 1 so I've put it all in one table but with three different seller ref middle numbers. I was also getting lost with the character manipulation of that field so I've cheated by splitting into two columns. Here's the result and if it's not what you want I hope the MATCH example may help.

Sheller3.xlsx
ABCDEFG
1SHIPMENT_START1ST DAY FOLLOWING MONTHSELLER_REF_NOWork1Work2CURRENT/AHEAD FORMULASHOULD READ
201-Feb-2101-Mar-21S-12345-11S-12345CURRENTCURRENT
301-Mar-2101-Mar-21S-12345-22S-12345CURRENTCURRENT
401-Apr-2101-Mar-21S-12345-33S-12345CURRENTCURRENT
501-May-2101-Mar-21S-12345-44S-12345CURRENTCURRENT
601-Jun-2101-Mar-21S-12345-55S-12345CURRENTCURRENT
701-Jul-2101-Mar-21S-12345-66S-12345CURRENTCURRENT
801-May-2101-Mar-21S-67899-22S-67899AHEADAHEAD
901-Jun-2101-Mar-21S-67899-33S-67899AHEADAHEAD
1001-Jul-2101-Mar-21S-67899-44S-67899AHEADAHEAD
1101-May-2101-Mar-21S-44444-11S-44444CURRENTCURRENT
1201-Jun-2101-Mar-21S-44444-22S-44444CURRENTCURRENT
1301-Jul-2101-Mar-21S-44444-33S-44444CURRENTCURRENT
Sheet1 (2)
Cell Formulas
RangeFormula
D2:D13D2=RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3))
E2:E13E2=LEFT([@[SELLER_REF_NO]],FIND("-",[@[SELLER_REF_NO]],3)-1)
F2:F13F2=IF(AND([@Work1]<>"1", ISNA(MATCH([@Work2]&"-1",SELLER_REF_NO,0)),EDATE([@[SHIPMENT_START]],-1)>DATE(YEAR(TODAY()),MONTH(TODAY())-1+RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3)),1)), "AHEAD", "CURRENT")
Named Ranges
NameRefers ToCells
'Sheet1 (2)'!SELLER_REF_NO='Sheet1 (2)'!$C$2:$C$13F2:F13, D2:E2
'Sheet1 (2)'!SHIPMENT_START='Sheet1 (2)'!$A$2:$A$13F2
Work1='Sheet1 (2)'!$D$2:$D$13F2
Work2='Sheet1 (2)'!$E$2:$E$13F2
 
Upvote 0
Toadstool said:
I need to let this go....

I was also confused by the 3 tables in 1 so I've put it all in one table but with three different seller ref middle numbers. I was also getting lost with the character manipulation of that field so I've cheated by splitting into two columns. Here's the result and if it's not what you want I hope the MATCH example may help.

Sheller3.xlsx
ABCDEFG
1SHIPMENT_START1ST DAY FOLLOWING MONTHSELLER_REF_NOWork1Work2CURRENT/AHEAD FORMULASHOULD READ
201-Feb-2101-Mar-21S-12345-11S-12345CURRENTCURRENT
301-Mar-2101-Mar-21S-12345-22S-12345CURRENTCURRENT
401-Apr-2101-Mar-21S-12345-33S-12345CURRENTCURRENT
501-May-2101-Mar-21S-12345-44S-12345CURRENTCURRENT
601-Jun-2101-Mar-21S-12345-55S-12345CURRENTCURRENT
701-Jul-2101-Mar-21S-12345-66S-12345CURRENTCURRENT
801-May-2101-Mar-21S-67899-22S-67899AHEADAHEAD
901-Jun-2101-Mar-21S-67899-33S-67899AHEADAHEAD
1001-Jul-2101-Mar-21S-67899-44S-67899AHEADAHEAD
1101-May-2101-Mar-21S-44444-11S-44444CURRENTCURRENT
1201-Jun-2101-Mar-21S-44444-22S-44444CURRENTCURRENT
1301-Jul-2101-Mar-21S-44444-33S-44444CURRENTCURRENT
Sheet1 (2)
Cell Formulas
RangeFormula
D2:D13D2=RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3))
E2:E13E2=LEFT([@[SELLER_REF_NO]],FIND("-",[@[SELLER_REF_NO]],3)-1)
F2:F13F2=IF(AND([@Work1]<>"1", ISNA(MATCH([@Work2]&"-1",SELLER_REF_NO,0)),EDATE([@[SHIPMENT_START]],-1)>DATE(YEAR(TODAY()),MONTH(TODAY())-1+RIGHT([@[SELLER_REF_NO]],LEN([@[SELLER_REF_NO]])-FIND("-",[@[SELLER_REF_NO]],3)),1)), "AHEAD", "CURRENT")
Named Ranges
NameRefers ToCells
'Sheet1 (2)'!SELLER_REF_NO='Sheet1 (2)'!$C$2:$C$13F2:F13, D2:E2
'Sheet1 (2)'!SHIPMENT_START='Sheet1 (2)'!$A$2:$A$13F2
Work1='Sheet1 (2)'!$D$2:$D$13F2
Work2='Sheet1 (2)'!$E$2:$E$13F2
Click to expand...
I think that will work!! Tomorrow I'll review more in-depth but from first glance looks like it will fit the bill. Thank you SO much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Formula using If statement with Xmatch
Replies
1
Views
160
dharv3y78
D
U
  • Question
calculate sum or weighted average of data found using Xlookup
Replies
5
Views
241
awoohaw
awoohaw
M
  • Question
Auto adjust row heights based on specific column
Replies
1
Views
160
XLfriend777
XLfriend777
UpToTheGills
  • Solved
Formula to find last date of a given MONTH and return value from another cell on the same row
Replies
2
Views
368
UpToTheGills
UpToTheGills
G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
346
Greenbehindthecells
G

Forum statistics

Threads
1,214,980
Messages
6,122,563
Members
449,088
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top