In NetSuite, you can easily perform date arithmetic within saved searches by using Formula (Date) or Formula (Numeric) fields. The `INTERVAL` keyword allows you to add a specific number of days to a date. This is particularly useful when you need to adjust dates for calculations or comparisons in your reports.
Formula Syntax for Adding Days:
To add days to a date in NetSuite, use the following formula syntax: {date_field} + INTERVAL ‘X’ DAY
Where:
– {date_field}: The date field you want to modify (e.g., {trandate}, {custcol_atlas_wd_promise_date}, etc.).
– X : The number of days you want to add. Replace `X` with the desired value (e.g., `2` for 2 days).
- Example of Adding 2 Days to a Date:
- If you have a Promise Date (`{custcol_atlas_wd_promise_date}`) and you want to add 2 days to it, the formula would look like this:
{custcol_atlas_wd_promise_date} + INTERVAL ‘2’ DAY
This formula will return the Promise Date with an additional 2 days.
- Using `INTERVAL` with Date Arithmetic: You can also use the `INTERVAL` keyword in combination with other date fields for comparisons. For example, if you want to find the difference between the Transaction Date(`{trandate}`) and the Promise Date (`{custcol_atlas_wd_promise_date}`) plus an additional 2 days, the formula would look like this:
{trandate} – ({custcol_atlas_wd_promise_date} + INTERVAL ‘2’ DAY)
- This formula will return the difference in days between the Transaction Date and the Promise Date (with 2 extra days).
2.Subtraction Example:
To subtract days from a date, you can reverse the operator: {date_field} – INTERVAL ‘X’ DAY
- For instance, if you want to subtract 2 days from the Promise Date, use this formula: {custcol_atlas_wd_promise_date} – INTERVAL ‘2’ DAY