Fix NetSuite Delivery Date Formula: Today, Tomorrow

Your saved search formula returns only "Past Due" or "Upcoming" because {today} in NetSuite is a datetime stamp, it includes hours, minutes, and seconds. A date field like {custbody9} stores midnight (00:00:00). The two are never equal except in the split second after midnight, so every comparison falls through to the ELSE branch. The fix is to truncate both sides to date-only values before comparing.

Why the Equality Check Fails

Run a quick test: add a Formula (Text) result column with just {today} and another with {custbody9}. You'll see something like 07/15/2024 14:32:18 versus 07/15/2024 00:00:00. The CASE WHEN {custbody9} = {today} condition evaluates false every time.

The {today} token returns the exact server timestamp when the search executes. Date custom fields store midnight. Never compare them directly.

The Working Formula

Use TRUNC() to strip the time component from both sides. Paste this into a Formula (Text) result column:

CASE
  WHEN TRUNC({custbody9}) = TRUNC({today}) THEN 'Today'
  WHEN TRUNC({custbody9}) = TRUNC({today}) + 1 THEN 'Tomorrow'
  WHEN TRUNC({custbody9}) = TRUNC({today}) + 2 THEN 'In 2 Days'
  WHEN TRUNC({custbody9}) < TRUNC({today}) THEN 'Past Due'
  ELSE 'Upcoming'
END

Set Summary Type to Group if you want one row per sales order, or leave as None for line-level detail.

Verify the Custom Field Type First

Before trusting the formula, confirm {custbody9} is a Date field, not Date/Time or Free-Form Text.

1. Go to Customization > Lists, Records, & Fields > Transaction Body Fields
2. Edit custbody9 (or your delivery date field)
3. Type must be Date
4. Store Value should be checked (stored fields perform better in searches)

If the field is Date/Time, the formula still works, TRUNC() handles it. If it's Free-Form Text, you'll need TO_DATE({custbody9}, 'MM/DD/YYYY') inside the TRUNC(), but fix the field type instead. Changing a stored field's type wipes existing data; export values first, change type, then re-import via CSV.

Restrict to Business Days (Skip Weekends)

The request mentions "Friday is yesterday on Monday." NetSuite doesn't have a built-in IS_WEEKDAY() function, but you can calculate the day-of-week offset using TO_CHAR({today}, 'D') (1 = Sunday, 7 = Saturday).

Add a Formula (Numeric) criteria row to filter the search to only show orders where the delivery date falls on a weekday:

CASE
  WHEN TO_CHAR(TRUNC({custbody9}), 'D') IN ('1','7') THEN 0  -- Sunday or Saturday
  ELSE 1
END

Set the criteria to equal to 1. This excludes weekend delivery dates entirely. If you'd rather label weekend dates as "Weekend" instead of hiding them, extend the main CASE statement:

CASE
  WHEN TO_CHAR(TRUNC({custbody9}), 'D') IN ('1','7') THEN 'Weekend'
  WHEN TRUNC({custbody9}) = TRUNC({today}) THEN 'Today'
  WHEN TRUNC({custbody9}) = TRUNC({today}) + 1 THEN 'Tomorrow'
  WHEN TRUNC({custbody9}) = TRUNC({today}) + 2 THEN 'In 2 Days'
  WHEN TRUNC({custbody9}) < TRUNC({today}) THEN 'Past Due'
  ELSE 'Upcoming'
END

Test Each Leg Before Combining

Don't debug the full CASE statement at once. Add separate Formula (Text) columns for each condition:

Run the search. Verify each returns the expected rows. Then assemble the final formula. This isolates whether the issue is date comparison, field type, or data quality.

Common Pitfalls

• Time zone mismatch: {today} uses the company time zone (Setup > Company > General Preferences). If your warehouse operates in a different zone, the "Today" boundary shifts. Consider a custom custbody_delivery_date_tz field populated via workflow if this matters.
• Null delivery dates: Rows where {custbody9} is empty fall into ELSE 'Upcoming'. Add WHEN {custbody9} IS NULL THEN 'No Date' as the first WHEN clause if you need visibility.
• Saved search caching: After editing the formula, use Refresh (not browser reload) to force re-evaluation.

What to Check Next

If the formula still mislabels dates, export the raw {custbody9} and {today} values to Excel and compare the truncated dates side by side. Nine times out of ten, the data in custbody9 isn't what you think, it's a Date/Time field with a non-midnight timestamp, or the PO-driven update workflow isn't firing on all orders.