ANS-0162 · SAVED SEARCHES & FORMULAS
NetSuite Saved Search: Calculate Whole Days Between Dates
Accurately determine the number of full days between two date fields in NetSuite Saved Searches by eliminating time components.
Short answer
To calculate the whole number of days between two dates in a NetSuite Saved Search, use a formula that truncates the time portion from each date before subtracting them. The TRUNC function removes the time, and FLOOR ensures only complete days are counted, providing a precise duration.
Scenario
Users often need to calculate the exact number of full days between two date fields within a NetSuite Saved Search. The challenge arises when date fields include time components, which can lead to inaccurate day counts if not properly handled. A common requirement is to ensure that only complete 24-hour periods are considered when determining the duration.
Solution
To achieve an accurate calculation of whole days between two dates in a NetSuite Saved Search, employ a formula that first removes the time portion from each date. The TRUNC function is recommended for this purpose, followed by subtracting the two dates and applying the FLOOR function to round down to the nearest whole number of days. This formula ensures that only the date components are compared, providing a precise count of full days. The formula would appear as follows:
FLOOR(TRUNC({date1})-TRUNC({date2}))Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support