Thursday, June 20, 2019

Sample Formula for Subtracting Date Values to Get Seconds

Below is a sample formula if users are aiming to get the difference of date values in seconds. To add this to a saved search, one has to select 'Formula (Numeric)' for the 'Field' dropdown and add the following formula on the 'Formula' field.
                           
ABS(TO_DATE('12/04/2013 3:56:26','MM/dd/yyyy HH:mi:ss')-TO_DATE('12/04/2013 3:56:15','MM/dd/yyyy HH:mi:ss'))                         
                                               
This can also be used for the fields available for saved searches. For example:
    
ABS(TO_DATE({trandate})-TO_DATE({datecreated}))     

1 comment:

  1. NetSuite must have changed/broken something because this doesn't work anymore :(

    Check the following formula:
    ABS(TO_DATE('12/04/2013 3:55:30','MM/dd/yyyy HH:mi:ss')-TO_DATE('12/04/2013 3:55:00','MM/dd/yyyy HH:mi:ss')) * 100000
    Result: 34.7222222222222222222222222222222222222
    Expected result: 30

    Obviously way too far off in just 30 seconds to be a usable function :(

    ReplyDelete