1. Home
2. Questions
3. Tags
5. Users
6. Jobs
7. Companies
8. Unanswered
Teams

Ask questions, find answers and collaborate at work with Stack Overflow for Teams.
Try Teams for free Explore Teams
Teams
Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Return to Question

deleted 47 characters in body

Source Link

edited Dec 9, 2022 at 22:51

21
4

Final OutPutOutput:

 ROUND(((IF(DAY(Return_To_Work__c)  <= 15, DATE( 
  YEAR(Return_To_Work__c) ,
   MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+
  28 + MOD(((MONTH(Return_To_Work__c) -1) + 
  FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
    (MONTH(Return_To_Work__c) -1)) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c) 
  -1))))
  ,IF(DAY(Return_To_Work__c)  >= 16,DATE( YEAR(Return_To_Work__c) 
    ,MONTH(Return_To_Work__c),(DAY(Return_To_Work__c)- 
   DAY(Return_To_Work__c)+28 + MOD(((MONTH(Return_To_Work__c) -1) + 
   + FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
   (MONTH(Return_To_Work__c) -1)) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c) -1)))),NULL)) -
   IF(DAY(First_Day_Of_Leave__c)  <= 15, DATE( 
 YEAR(First_Day_Of_Leave__c) 
  , MONTH(First_Day_Of_Leave__c) , 
 (DAY(First_Day_Of_Leave__c)- 
  DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c)  >= 
    16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
   DAY(First_Day_Of_Leave__c)+1)),NULL)))/30),000)

Final OutPut:

 ROUND(((IF(DAY(Return_To_Work__c)  <= 15, DATE( 
  YEAR(Return_To_Work__c) ,
   MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+
  28 + MOD(((MONTH(Return_To_Work__c) -1) + 
  FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
    (MONTH(Return_To_Work__c) -1)) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c) -1))))
  ,IF(DAY(Return_To_Work__c)  >= 16,DATE( YEAR(Return_To_Work__c) 
    ,MONTH(Return_To_Work__c),(DAY(Return_To_Work__c)- 
   DAY(Return_To_Work__c)+28 + MOD(((MONTH(Return_To_Work__c) -1) 
   + FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
   (MONTH(Return_To_Work__c) -1)) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c) -1)))),NULL)) -
   IF(DAY(First_Day_Of_Leave__c)  <= 15, DATE( 
 YEAR(First_Day_Of_Leave__c) , MONTH(First_Day_Of_Leave__c) , 
 (DAY(First_Day_Of_Leave__c)- 
 DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c)  >= 
   16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
  DAY(First_Day_Of_Leave__c)+1)),NULL)))/30),00)

Final Output:

 ROUND(((IF(DAY(Return_To_Work__c) <= 15, DATE( YEAR(Return_To_Work__c) ,
MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
 DAY(Return_To_Work__c)+
 28 + MOD(((MONTH(Return_To_Work__c) -1) + 
  FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
  (MONTH(Return_To_Work__c) -1)) + 2 * FLOOR(1/(MONTH(Return_To_Work__c) 
  -1))))
 ,IF(DAY(Return_To_Work__c) >= 16,DATE( YEAR(Return_To_Work__c) 
 ,MONTH(Return_To_Work__c),(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+28 + MOD(((MONTH(Return_To_Work__c)) + 
    FLOOR((MONTH(Return_To_Work__c))/8)), 2) + MOD(2, 
  (MONTH(Return_To_Work__c))) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c))))),NULL)) -
  IF(DAY(First_Day_Of_Leave__c) <= 15, DATE( YEAR(First_Day_Of_Leave__c) 
  , MONTH(First_Day_Of_Leave__c) ,(DAY(First_Day_Of_Leave__c)- 
  DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c) >= 
    16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
   DAY(First_Day_Of_Leave__c)+1)),NULL)))/30),0)

added 323 characters in body

Source Link

edited Dec 9, 2022 at 22:08

21
4

Updated formulaFinal OutPut: First Block

 ROUND(((IF(DAY(Return_To_Work__c)  <= 15, DATE( 
  YEAR(Return_To_Work__c) , 
   MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+30+
  28 + MOD(((MONTH(Return_To_Work__c) -1) + 
  FLOOR((MONTH(Return_To_Work__c) -1)/8)),IF 2) + MOD(DAY2, 
    (MONTH(Return_To_Work__c) -1)) + >=2 * 
   FLOOR(1/(MONTH(Return_To_Work__c) -1))))
  ,IF(DAY(Return_To_Work__c)  >= 16,DATE( YEAR(Return_To_Work__c) 
    ,MONTH(Return_To_Work__c),(DAY(Return_To_Work__c)- 
   DAY(Return_To_Work__c)+28 + MOD(((MONTH(Return_To_Work__c) -1) 
   + FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
   (DAYMONTH(Return_To_Work__c) -DAY1)) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c)+30 -1)))),NULL)) -

Second Block

   IF(DAY(First_Day_Of_Leave__c)  <= 15, DATE( 
   YEAR(First_Day_Of_Leave__c) , MONTH(First_Day_Of_Leave__c) , 
 (DAY(First_Day_Of_Leave__c)- 
 DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c)  >= 
   16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
   DAY(First_Day_Of_Leave__c)+1)),NULL)))/30),00)

Any Suggestions on how to get accurate months difference using first and second block

Updated formula: First Block

IF(DAY(Return_To_Work__c)  <= 15, DATE( YEAR(Return_To_Work__c) , 
 MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+30)),IF(DAY(Return_To_Work__c)  >= 
  16,DATE( YEAR(Return_To_Work__c) ,MONTH(Return_To_Work__c), 
  (DAY(Return_To_Work__c)-DAY(Return_To_Work__c)+30)),NULL))

Second Block

  IF(DAY(First_Day_Of_Leave__c)  <= 15, DATE( 
   YEAR(First_Day_Of_Leave__c) , MONTH(First_Day_Of_Leave__c) , 
 (DAY(First_Day_Of_Leave__c)- 
 DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c)  >= 
  16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
   DAY(First_Day_Of_Leave__c)+1)),NULL))

Any Suggestions on how to get accurate months difference using first and second block

Final OutPut:

 ROUND(((IF(DAY(Return_To_Work__c)  <= 15, DATE( 
  YEAR(Return_To_Work__c) ,
   MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+
  28 + MOD(((MONTH(Return_To_Work__c) -1) + 
  FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
    (MONTH(Return_To_Work__c) -1)) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c) -1))))
  ,IF(DAY(Return_To_Work__c)  >= 16,DATE( YEAR(Return_To_Work__c) 
    ,MONTH(Return_To_Work__c),(DAY(Return_To_Work__c)- 
   DAY(Return_To_Work__c)+28 + MOD(((MONTH(Return_To_Work__c) -1) 
   + FLOOR((MONTH(Return_To_Work__c) -1)/8)), 2) + MOD(2, 
   (MONTH(Return_To_Work__c) -1)) + 2 * 
   FLOOR(1/(MONTH(Return_To_Work__c) -1)))),NULL)) -
   IF(DAY(First_Day_Of_Leave__c)  <= 15, DATE( 
 YEAR(First_Day_Of_Leave__c) , MONTH(First_Day_Of_Leave__c) , 
 (DAY(First_Day_Of_Leave__c)- 
 DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c)  >= 
   16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
  DAY(First_Day_Of_Leave__c)+1)),NULL)))/30),00)

added 856 characters in body

Source Link

edited Dec 9, 2022 at 0:03

21
4

Here is the APEX code version to be more clearUpdated formula: First Block

Date Firstdayofleave;
Date ReturnToWork;
Date FirstDayOfLeaves=Date.newinstance(2022,12,26);
Date ReturnTWorks=Date.newinstance(2023,02,28);
if(FirstDayOfLeaves.day() <=15 && FirstDayOfLeaves.day() >=1){
    Firstdayofleave=FirstDayOfLeaves;
    System.debug('if block'+Firstdayofleave);
}else{
    if(FirstDayOfLeaves.day() >=16 && FirstDayOfLeaves.day() <=31){
        Firstdayofleave =FirstDayOfLeaves.addMonths( 1 );
        System.debug('else block'+Firstdayofleave);
    }
}
if(ReturnTWorks.day() <=15 && ReturnTWorks.day() >=1){
    ReturnToWork =ReturnTWorks.addMonths( 1 );
    System.debug('if block'+ReturnToWork);
}else{
    if(ReturnTWorks.day() >=16 && ReturnTWorks.day() <=31){
        ReturnToWork =ReturnTWorks;
        System.debug('else block'+ReturnToWork);
    }
}
Integer monthDiff = Firstdayofleave.monthsBetween(ReturnToWork);
if (ReturnToWork.day() > Firstdayofleave.day()) monthDiff++;
    System.debug(monthDiff);

IF(DAY(Return_To_Work__c)  <= 15, DATE( YEAR(Return_To_Work__c) , 
 MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+30)),IF(DAY(Return_To_Work__c)  >= 
  16,DATE( YEAR(Return_To_Work__c) ,MONTH(Return_To_Work__c), 
  (DAY(Return_To_Work__c)-DAY(Return_To_Work__c)+30)),NULL))

Second Block

  IF(DAY(First_Day_Of_Leave__c)  <= 15, DATE( 
   YEAR(First_Day_Of_Leave__c) , MONTH(First_Day_Of_Leave__c) , 
 (DAY(First_Day_Of_Leave__c)- 
 DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c)  >= 
  16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
   DAY(First_Day_Of_Leave__c)+1)),NULL))

Any Suggestions on how to get accurate months difference using first and second block

Here is the APEX code version to be more clear

Date Firstdayofleave;
Date ReturnToWork;
Date FirstDayOfLeaves=Date.newinstance(2022,12,26);
Date ReturnTWorks=Date.newinstance(2023,02,28);
if(FirstDayOfLeaves.day() <=15 && FirstDayOfLeaves.day() >=1){
    Firstdayofleave=FirstDayOfLeaves;
    System.debug('if block'+Firstdayofleave);
}else{
    if(FirstDayOfLeaves.day() >=16 && FirstDayOfLeaves.day() <=31){
        Firstdayofleave =FirstDayOfLeaves.addMonths( 1 );
        System.debug('else block'+Firstdayofleave);
    }
}
if(ReturnTWorks.day() <=15 && ReturnTWorks.day() >=1){
    ReturnToWork =ReturnTWorks.addMonths( 1 );
    System.debug('if block'+ReturnToWork);
}else{
    if(ReturnTWorks.day() >=16 && ReturnTWorks.day() <=31){
        ReturnToWork =ReturnTWorks;
        System.debug('else block'+ReturnToWork);
    }
}
Integer monthDiff = Firstdayofleave.monthsBetween(ReturnToWork);
if (ReturnToWork.day() > Firstdayofleave.day()) monthDiff++;
    System.debug(monthDiff);

Updated formula: First Block

IF(DAY(Return_To_Work__c)  <= 15, DATE( YEAR(Return_To_Work__c) , 
 MONTH(Return_To_Work__c) -1,(DAY(Return_To_Work__c)- 
  DAY(Return_To_Work__c)+30)),IF(DAY(Return_To_Work__c)  >= 
  16,DATE( YEAR(Return_To_Work__c) ,MONTH(Return_To_Work__c), 
  (DAY(Return_To_Work__c)-DAY(Return_To_Work__c)+30)),NULL))

Second Block

  IF(DAY(First_Day_Of_Leave__c)  <= 15, DATE( 
   YEAR(First_Day_Of_Leave__c) , MONTH(First_Day_Of_Leave__c) , 
 (DAY(First_Day_Of_Leave__c)- 
 DAY(First_Day_Of_Leave__c)+1)),IF(DAY(First_Day_Of_Leave__c)  >= 
  16,DATE( YEAR(First_Day_Of_Leave__c) 
  ,MONTH(First_Day_Of_Leave__c)+1,(DAY(First_Day_Of_Leave__c)- 
   DAY(First_Day_Of_Leave__c)+1)),NULL))

Any Suggestions on how to get accurate months difference using first and second block

Formatted & cleaned up Apex

Source Link

edited Dec 8, 2022 at 3:41

2.7k
6
15
29

Loading

edited body

Source Link

edited Dec 7, 2022 at 22:24

21
4

Loading

added 1101 characters in body

Source Link

edited Dec 7, 2022 at 22:19

21
4

Loading

added 101 characters in body

Source Link

edited Dec 7, 2022 at 21:44

21
4

Loading

deleted 37 characters in body

Source Link

edited Dec 7, 2022 at 21:21

21
4

Loading

Various readability improvements

Source Link

edited Dec 7, 2022 at 19:32

2.7k
6
15
29

Loading

added 240 characters in body

Source Link

edited Dec 7, 2022 at 19:19

21
4

Loading

Source Link

asked Dec 7, 2022 at 19:05

21
4

Loading