000033923 - How to count the number of business days between two Date fields using an RSA Archer calculated field

Document created by RSA Customer Support Employee on Sep 5, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000033923
Applies ToRSA Product Set: Archer
RSA Version/Condition: 5.x, 6.x
Resolution

  1. Enable the Calculated option for a field.


  2. Copy/paste the following formula after replacing From Date and To Date with your 2 Date fields.
    DATEDIF([From Date] , [To Date] , DAY )+1 
    - IF(OR( 
    AND ( WEEKDAY([To Date]) = "Monday" , WEEKDAY([From Date]) = "Saturday" ) 
    , AND ( WEEKDAY([To Date]) = "Tuesday" , WEEKDAY([From Date]) = "Saturday") 
    , AND ( WEEKDAY([To Date]) = "Wednesday" , WEEKDAY([From Date]) = "Saturday")
    , AND ( WEEKDAY([To Date]) = "Thursday" , WEEKDAY([From Date]) = "Saturday") 
    , AND ( WEEKDAY([To Date]) = "Friday" , WEEKDAY([From Date]) = "Saturday") 
    , AND ( WEEKDAY([To Date]) = "Monday" , WEEKDAY([From Date]) = "Friday" ) 
    , AND ( WEEKDAY([To Date]) = "Tuesday" , WEEKDAY([From Date]) = "Friday") 
    , AND ( WEEKDAY([To Date]) = "Wednesday" , WEEKDAY([From Date]) = "Friday") 
    , AND ( WEEKDAY([To Date]) = "Thursday" , WEEKDAY([From Date]) = "Friday") 
    , AND ( WEEKDAY([To Date]) = "Monday" , WEEKDAY([From Date]) = "Thursday") 
    , AND ( WEEKDAY([To Date]) = "Tuesday" , WEEKDAY([From Date]) = "Thursday") 
    , AND ( WEEKDAY([To Date]) = "Wednesday" , WEEKDAY([From Date]) = "Thursday") 
    , AND ( WEEKDAY([To Date]) = "Monday" , WEEKDAY([From Date]) = "Wednesday") 
    , AND ( WEEKDAY([To Date]) = "Tuesday" , WEEKDAY([From Date]) = "Wednesday") 
    , AND ( WEEKDAY([To Date]) = "Monday" , WEEKDAY([From Date]) = "Tuesday") 
    , AND ( WEEKDAY([To Date]) = "Sunday" , WEEKDAY([From Date]) = "Monday") 
    , AND ( WEEKDAY([To Date]) = "Sunday" , WEEKDAY([From Date]) = "Tuesday") 
    , AND ( WEEKDAY([To Date]) = "Sunday" , WEEKDAY([From Date]) = "Wednesday") 
    , AND ( WEEKDAY([To Date]) = "Sunday" , WEEKDAY([From Date]) = "Thursday") 
    , AND ( WEEKDAY([To Date]) = "Sunday" , WEEKDAY([From Date]) = "Friday") 
    , AND ( WEEKDAY([To Date]) = "Sunday" , WEEKDAY([From Date]) = "Saturday") 
    ),2,0) 
    - TRUNC(DATEDIF([From Date] , [To Date] , DAY )/7) * 2 
    - IF (Weekday ([From Date])="Sunday",1,0) 
    - IF (Weekday ([To Date])="Saturday",1,0)


Attachments

    Outcomes