Skip to main content
Topics#Data ManagementDomanda di T O
T O ha fatto una domanda in #Data Management
19 nov 2015, 14:45

Formula breakdown

CASE(MOD( First_Date_of_Holiday__c - DATE(1985,6,24),7), 

 

0 , CASE( MOD( Last_Date_of_Holiday__c - First_Date_of_Holiday__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 

 

1 , CASE( MOD( Last_Date_of_Holiday__c - First_Date_of_Holiday__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 

 

2 , CASE( MOD( Last_Date_of_Holiday__c - First_Date_of_Holiday__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 

 

3 , CASE( MOD( Last_Date_of_Holiday__c - First_Date_of_Holiday__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 

 

4 , CASE( MOD( Last_Date_of_Holiday__c - First_Date_of_Holiday__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 

 

5 , CASE( MOD( Last_Date_of_Holiday__c - First_Date_of_Holiday__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 

 

6 , CASE( MOD( Last_Date_of_Holiday__c - First_Date_of_Holiday__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 

 

999) 

 

+ 

 

(FLOOR(( Last_Date_of_Holiday__c - First_Date_of_Holiday__c )/7)*5)- No_of_Bank_Holiday_s_within_Leave__c

 

Hi

 

Can someone be a gem and break this down line by line please?

 

Thanks
3 risposte
  1. Valentin SCHMITT (Moet Hennessy)
    19 nov 2015, 15:04
    Hello, 

     

    At the first step ​CASE(MOD( First_Date_of_Holiday__c - DATE(1985,6,24),7), it gives a result between 0 and 6 which gives you the ability to know the day of the week. 24th June 1985 was a Monday (I didn't know that ..). So if First_Date_of_Holiday__c is also a Monday it will refer to the first line with "0" and if it's a Thursday it will refer to line with number "3" 

     

    The rest of the formula works the same but tell me if you need more explanations 
Scrivi una risposta...
0/9000