ANS-0056 · SAVED SEARCHES & FORMULAS
How to Exclude Weekends from Date Results in NetSuite Saved Searches?
Learn how to ensure date results in NetSuite saved searches always fall on a weekday by leveraging string manipulation and a CASE formula.
Short answer
To ensure date results in a NetSuite saved search are always weekdays, use a CASE formula with the INSTR function. This approach treats the day of the week as a string, allowing you to identify and adjust weekend dates. For instance, if a date falls on a Saturday, the formula can automatically advance it to the following Monday.
Scenario
A common requirement in NetSuite saved searches is to ensure that all date results fall on a weekday. This means excluding Saturday and Sunday from the output. For example, if a specific date, such as a due date, happens to be a Saturday, the system needs to automatically provide the next business day instead.
Solution
This can be achieved by treating the days of the week as a string of data and utilizing the INSTR function within a CASE formula. This method allows for conditional logic to adjust dates that fall on weekends. For example, to ensure a due date that falls on a Saturday is adjusted to the following Monday, implement the following formula in your saved search:CASE WHEN INSTR(to_char({customrecord_date}, 'DAY'),'SATURDAY') != 0 THEN {customrecord_date}+2 ELSE {customrecord_date} END This formula checks if the day of the week for {customrecord_date} is 'SATURDAY'. If it is, it adds two days to the date, effectively moving it to Monday. Otherwise, the original date is retained. The result will be the date of the following Monday if the original date was a Saturday.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support