Helpful Formula - Business Days Calculation, Date_Diff

Content

I was disappointed in the limited capability of the rel_date_diff function. (It can’t be nested in other expressions.) Instead I came up with this formula for calculating the number of business days between two dates that I’ve not seen anyone else in the community reference.

 

((DATE_DIFF(date2,date1)/86400) + 1) - ((to_number(date_format(date2,'WW')) - to_number(date_format(date1,'WW')))* 2) - IF(Date_format(date1,'DAY') = 'Sunday',1,0) - IF(Date_format(date2,'DAY') = 'Saturday',1,0)

 

Happy Reporting,

 

Subject edited by Danette Beal for clarification

Version

RightNow

Code Snippet

             ((DATE_DIFF(date2,date1)/86400) + 1) - ((to_number(date_format(date2,'WW')) - to_number(date_format(date1,'WW')))* 2) - IF(Date_format(date1,'DAY') = 'Sunday',1,0) - IF(Date_format(date2,'DAY') = 'Saturday',1,0)