Scenario
User want to add a Formula field to a Saved Search that would show the difference between two date fields, but only display business days and not include weekends.
Solution
In a Formula (Numeric) field, add this formula:
- ROUND(((TO_CHAR({enddate}, ‘J’) – TO_CHAR({startdate}, ‘J’))) – ((((TRUNC({enddate}, ‘D’)) – (TRUNC({startdate}, ‘D’)))/7)*2)-(CASE WHEN TO_CHAR({startdate}, ‘DY’) = ‘SUN’ THEN 1 ELSE 0 END) – (CASE WHEN TO_CHAR({enddate}, ‘DY’) = ‘SAT’ THEN 1 ELSE 0 END), 0)
Note that the formula above assumes that the {enddate} is the same, or after the {startdate}.
If you have some cases where the {enddate} is before the {startdate}, then the formula might give inaccurate results. If you want to show the absolute value of the difference of these two date fields, then you can use this formula instead:
- ROUND(((TO_CHAR((CASE WHEN {startdate} > {enddate} THEN {startdate} ELSE {enddate} END), ‘J’) – TO_CHAR((CASE WHEN {startdate} < {enddate} THEN {startdate} ELSE {enddate} END), ‘J’))) + MOD(((CASE WHEN {startdate} > {enddate} THEN {startdate} ELSE {enddate} END) -(CASE WHEN {startdate} < {enddate} THEN {startdate} ELSE {enddate} END)), 1) – ((((TRUNC((CASE WHEN {startdate} > {enddate} THEN {startdate} ELSE {enddate} END), ‘D’)) – (TRUNC((CASE WHEN {startdate} < {enddate} THEN {startdate} ELSE {enddate} END), ‘D’)))/7)*2)-(CASE WHEN TO_CHAR((CASE WHEN {startdate} < {enddate} THEN {startdate} ELSE {enddate} END), ‘DY’) = ‘SUN’ THEN 1 ELSE 0 END) – (CASE WHEN TO_CHAR((CASE WHEN {startdate} > {enddate} THEN {startdate} ELSE {enddate} END), ‘DY’) = ‘SAT’ THEN 1 ELSE 0 END), 0)
Notes:
- Replace {startdate} and {enddate} with the field IDs of the date fields that you will compare.
- The given formula will only count the weekdays, and do not consider holidays.