0

I am trying to create a formula field with the below conditions.

Ex: If start date is 1/1/2017 and end date is 5/13/2017. I should get the result as 4.5 If start date is 1/1/2017 and end date is 5/25/2017. I should get the result as 5.0

I created below formula field which is working for the upper date( greater than 15)

IF(
    DAY(End_Date__c) <= 15, 
    ROUND(
        ((End_Date__c - Start_Date__c) / 30),
        1
    ),
    IF(
        NOT(ISBLANK(End_Date__c)) && NOT(ISBLANK(Start_Date__c)),
        (
            ((YEAR(End_Date__c) - YEAR(Start_Date__c) - 1) * 12)
            + (12 - MONTH(Start_Date__c) +1) 
            + MONTH(End_Date__c)
        ),
        null
    )
)

I know that there is an issue my first if condition. The round function is giving result as 4.2 (if date is 8), 4.4 (if date is 12). Can someone suggest me how to fix it.

  • so, you want date difference will be as 4.5, 5.0, 5.5, 6.0 like this? – Santanu Boral May 17 '17 at 15:18
  • Yes. if start date is 1/1/2017 and end date is 1/14/ 2017 the result should be 0.5 if start date is 1/1/2017 and end date is 1/28/2017 the result should be 1.0. If start date is 2/2/2017 and end date is 2/12/2017 the result should be 1.5 , if start date us 2/2/2017 and end date is 2/20/2017 the result should be 2.0 – KSL May 17 '17 at 15:21
0

You were close:

( YEAR( End_Date__c) - YEAR( Start_Date__c)
 + IF(MONTH(DATEVALUE(CreatedDate)) < MONTH(End_Date__c), 1, 0)
) * 12
- MONTH(DATEVALUE(CreatedDate))
+ MONTH(End_Date__c)
+ IF(DAY(End_Date__c) > 15, 0.5, 0)
+ IF(DAY(Start_Date__c) > 15, -0.5, 0)
+ 0.5

P.S. I assumed you may also want to subtract 0.5 month if start date day > 15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.