Calculate the age of a transaction from the date it was created in business days excluding weekends
Published 03/28/2014 04:43 PM   |    Updated 12/25/2017 02:00 PM	  |    Answer Id: 37724
To do this you must use the formula below in a transaction saved search:

1. Navigate to Lists > Search > Saved Searches > New > Transaction
2. Under the Criteria tab > Standard sub tab, select Type and set it to the transaction type you want to view.
3. Under the Results tab > Columns sub tab:
Select Formula (Text) for the age of transaction excluding weekends in Days-Hours- Mins format and use the formula below

TRUNC( ((TO_CHAR({today},'J') -    TO_CHAR({datecreated},'J')))+MOD(({today}- {datecreated} ) ,1) -   ((((TRUNC({today},'D'))-(TRUNC({datecreated},'D')))/7)*2) -  (CASE WHEN TO_CHAR({datecreated},'DY')='SUN' THEN 1 ELSE 0 END) -  (CASE WHEN TO_CHAR({today},'DY')='SAT' THEN 1 ELSE 0 END)) || ' days ' || TRUNC(MOD(({today}- {datecreated} ) ,1)*24) || ' hrs ' || TRUNC(MOD(MOD(({today}- {datecreated} ) ,1)*24,1)*60) || ' mins'


Select Formula (Numeric) for age of transaction excluding weekends in raw number format(rounded to two decimals) and use the formula below

ROUND(((TO_CHAR({today},'J') -    TO_CHAR({datecreated},'J')))+MOD(({today}- {datecreated} ) ,1) -   ((((TRUNC({today},'D'))-(TRUNC({datecreated},'D')))/7)*2) -  (CASE WHEN TO_CHAR({datecreated},'DY')='SUN' THEN 1 ELSE 0 END) -  (CASE WHEN TO_CHAR({today},'DY')='SAT' THEN 1 ELSE 0 END),2)

4. Rename the Search Title and hit Save & Run

Notes:

1. Should you wish to use the formula as a filter, use the raw number format.
2. You may replace the {datecreated} field should you wish to use another date to compute the age from.