Register today for Oracle CloudWorld. October 17th - 20th, Las Vegas

Register now

Hillel Cooperman, Senior VP of User Experience Design, has a message for you on Oracle CloudWorld

Watch now
Helpful Formula - Business Days Calculation, Date_Diff — Cloud Customer Connect
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Helpful Formula - Business Days Calculation, Date_Diff

Received Response
73
Views
14
Comments
edited Jun 8, 2022 8:32AM in Agent Desktop 14 comments

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) 
        

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!