Discussions
Stay up-to-date with the latest news from NetSuite. You’ll be in the know about how to connect with peers and take your business to new heights at our virtual, in-person, on demand events, and much more.
Now is the time to ask your NetSuite-savvy friends and colleagues to join the NetSuite Support Community! Refer now! Click here to watch and learn more!
NSC | Formula that Compares Expected Close Date & Actual Close Date in an Opportunity Search
Scenario
User is creating an Opportunity Saved Search and needs a formula that shows count of days between Expected Close Date and Actual Close Date fields. The formula should show:
- the days Past Expected Close Date
- the days Before Expected Close Date
- if the Actual and Expected Close Dates match
Solution
In the Results tab of the Saved Search, use Formula (Numeric) fields with the below formula:
- Days past expected: CASE WHEN {expectedclosedate} < {closedate} THEN {closedate} - {expectedclosedate} END
- Days before expected: CASE WHEN {expectedclosedate} > {closedate} THEN ABS({closedate} - {expectedclosedate}) END
- Dates match: CASE WHEN {expectedclosedate} = {closedate} THEN 0 END
Learn how to Refer A Member | Earn the Answer Accepter Badge | Be the Content Creator of the Quarter | Vote for the content you want to see!
0