Skip to main content
Topics#FormulasPrabhata Kumar Rath's Question
Prabhata Kumar Rath (Accenture Solutions Pvt. Ltd.) asked in #Formulas
Sep 14, 2016, 7:47 AM

Calculate Working day or Business Day in current month excluding both Weekends and Public Holidays.

Hi Guys,

I have a requirement like we have to calculate current working day (e.g. 4th or 5th working day) excluding WEEKENDS and PUBLIC HOLIDAYS. For excluding weekends, there is a formula. But for excluding public holidays ,I am not sure how to write a formula for the same.

Currently to calculate current business day excluding weekends, I am using the formula (retrieved from Salesforce Help) given below:

( 
( FLOOR ( ( TODAY() - DATE (1900,01,01) ) / 7 ) *5 ) 
+ MIN ( 5, MOD ( TODAY() - DATE (1900,01,01), 7 ) +1 ) 
) - 
( 
( 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 ) ) 
)

So now, I request for the possible solutions (both Configuration and customization way). Configuration way is preferred here.

Waiting for your response.

Thanks in Advance.

Prabhata
2 answers
  1. Ashapriya gadi (Auspost)
    Nov 8, 2017, 5:32 PM
    Hi Prabhata,

    Could you post your solution if you had figured out how to include holidays in determining the business day?

    Thanks

    Asha
Write an answer...
0/9000