0

I have two dates

  1. startdate : 12/26/2022
  2. enddate : 02-28-2023

if the start date is 1-15th day then the result should be the current month ex: mm-dd-yyyy[12-11-2022] Then December 1st

if the start date is 16th-31st day then the result should be the current month EX: mm-dd-yyyy[12-22-2022] Then January 1st.

if the EndDate is 1-15th day then the result should be the current month+1 EX: mm-dd-yyyy[02-11-2022] Then January 31st

if the EndDate is 16-31sh day then the result should be the current month+1 EX: mm-dd-yyyy[02-20-2022] Then Feb 28th

input start date result: December 1st

input end date result: Feb 28th

I am looking for difference in the months from startdate and enddate as per given if conditions

  1. startdate : 12/26/2022
  2. enddate : 02-28-2023 Expected Output/result : [2]

Can we do this in the formula field? I am able to do it in apex it worked but I was unable to do it in the formula field any help would be appreciated.

Formula field i tried:

IF(DAY(EndDate__c)  <= 15,(MONTH(EndDate__c)),IF(DAY(EndDate__c)  >= 16,(MONTH(EndDate__c)+1),NULL )) -
IF(DAY(StartDate__c)  <= 15,(MONTH(StartDate__c)),IF(DAY(StartDate__c)  >= 16,(MONTH(StartDate__c)+1),NULL ))

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)
Improve this question
10
  • 1
    What was the result of your formula? Syntax error? Error when on the record page? Unexpected results?
    – Moonpie
    Commented Dec 7, 2022 at 19:36
  • the result was unexpected -9.00. Insted 3 StartDate is 12/26/2022 enddate: 2/28/2023
    – Sri
    Commented Dec 7, 2022 at 20:40
  • 3
    Your question is very confusing. Are you trying to return 3 values from the formula (start date result, end date result, and Result[3])? What is Result[3]? Also your line about Start Date day 15-31 has conflicting info between "current month" and January 1. Please edit your post to clarify everything and include more detail.
    – David Cheng
    Commented Dec 7, 2022 at 20:58
  • Have David Cheng, sure I will try to be more clear i will edit the post again
    – Sri
    Commented Dec 7, 2022 at 21:19
  • 2
    This question seems eerily similar to this recent question from a new person: salesforce.stackexchange.com/q/391514/81648
    – Moonpie
    Commented Dec 8, 2022 at 3:38

1 Answer 1

Reset to default
0 
 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)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .