Skip to main content
Topics#Data ManagementLindsay MacArthur's Question
Lindsay MacArthur (PPG Industries) asked in #Data Management
Oct 6, 2017, 8:29 PM

Formula to calculate number of working days in the month of a specific date field

I'm looking to create a formula field that returns the number of working days in the month of a specific date (not just today). I found this formula below but I don't want it to calculate based off today but rather based off of a date field called Sales_Date__c 

 

(

 

( FLOOR ( ( DATE (

 

YEAR (DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),

 

MONTH(DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),

 

1 ) - 1 - DATE (1900,01,01) ) / 7 ) *5 )

 

+ MIN ( 5, MOD ( DATE (

 

YEAR (DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),

 

MONTH(DATE (YEAR(TODAY()),MONTH(TODAY()),28)+5),

 

1 ) - 1 - DATE (1900,01,01), 7 ) )

 

) -

 

(

 

( FLOOR ( ( DATE(YEAR(TODAY()),MONTH(TODAY()),1) - DATE (1900,01,01) ) / 7 ) *5 )

 

+ MIN ( 5, MOD ( DATE(YEAR(TODAY()),MONTH(TODAY()),1) - DATE (1900,01,01), 7 ) )

 

)
10 answers
  1. Steve Molis (You Owe Me a Beer, Inc.)
    Oct 6, 2017, 11:47 PM
    Another way to get around the Compile Size limit is to crate a Datatype = Number field instead of a Formula(Number).  

     

    Then use a Workflow Rule or the Process Builder triggered when the  Sales_Date__c is updated or changed, then add an Immedite Action(Field Update) that uses your Formula to update the Number Field
Write an answer...
0/9000