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.
New AI Community Guidelines. Please review and follow them to ensure AI use stays safe, accurate, and compliant.
Keep an eye out for upcoming NetSuite events, including meetups, workshops, and webinars. These sessions are a great way to connect with peers, learn from experts, and stay current on the latest NetSuite updates and best practices. Registration links are provided in each event.
NSC | Saved Search Formula > Number of Weekdays between Two Dates
Scenario
User want to add a Formula field to a Saved Search that would show the difference between two date fields, but only display business days and not include weekends.
Solution
In a Formula (Numeric) field, add this formula:
- ROUND(((TO_CHAR({enddate}, 'J') - TO_CHAR({startdate}, 'J'))) - ((((TRUNC({enddate}, 'D')) - (TRUNC({startdate}, 'D')))/7)*2)-(CASE WHEN TO_CHAR({startdate}, 'DY') = 'SUN' THEN 1 ELSE 0 END) - (CASE WHEN TO_CHAR({enddate}, 'DY') = 'SAT' THEN 1 ELSE 0 END), 0)
Note that the formula above assumes that the {enddate} is the same, or after the {startdate}.
If you have some cases where the {enddate} is before the {startdate}, then the formula might give inaccurate results. If you want to show the
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