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.
Do you have a different formula that has the same functionality? Feel free to share them here!
-----
If you find this information useful, let us know by reacting or commenting on this post!