5

My requirement is exactly looking like This question. The question has solution almost except in one case.

The issue is, If we select 31st march, Then the result should like 31st march+ 6 months, which means 31st September. But we do not have this date(31st sep). So, then the result should be 1st October. But in this its giving Error as in result. This is giving error in all 31st dates except January 31st. (Beacuse 31st January + 6 months is equal to July 31st ).

The formula is given below.

if( MONTH(DATEVALUE( CreatedDate ))> 6,  DATETIMEVALUE(TEXT(YEAR(DATEVALUE( CreatedDate ))+1)+"-" +TEXT((MONTH(DATEVALUE( CreatedDate ))+6)-12)+"-" +TEXT(DAY( DATEVALUE( CreatedDate )))+" "+TEXT(VALUE( MID( TEXT( CreatedDate - 5 ), 12, 2 ) ))+":"+ MID( TEXT( CreatedDate - 5 ), 15, 2 )+":"+ MID( TEXT( CreatedDate - 5 ), 18, 2 ) ), DATETIMEVALUE(TEXT(YEAR(DATEVALUE( CreatedDate )))+"-" +TEXT(MONTH(DATEVALUE( CreatedDate ))+ 6)+"-" +TEXT(DAY( DATEVALUE( CreatedDate )))+" "+TEXT(VALUE( MID( TEXT( CreatedDate - 5 ), 12, 2 ) ))+":"+ MID( TEXT( CreatedDate - 5 ), 15, 2 )+":"+ MID( TEXT( CreatedDate - 5 ), 18, 2 ) ))

Please suggest me, How to resolve the error....?

2

The below is taken from Sample Date Formulas in the Using Date and Date/Time Formulas Tip Sheet.

This formula does the following:

  • Returns March 1 if the future month is a February and the day is greater than 28. This portion of the formula performs the same for both leap and non-leap years.
  • Returns the first day of the next month if the future month is April, June, September, or November and the day is greater than 30.
  • Otherwise, it returns the correct date in the future month.

This example formula adds two months to a given date. You can modify the conditions on this formula if you prefer different behaviors for dates at the end of the month.

You can easily modify what's below to give you what you want for 6 months or any other number of months between two dates with the behavior that you want to see.

  DATE(

  YEAR( ***date*** ) + FLOOR( ( MONTH ( date ) + 2 - 1 ) / 12 ),

  MOD( MONTH ( date ) + 2 - 1 + 
    IF( DAY ( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1, 

      2, 28,

      4, 30,

      6, 30,

      9, 30, 

      11, 30,

      31 ), 1, 0 ), 12 ) + 1,

    IF( DAY( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1,

      2, 28, 

      4, 30, 

      6, 30, 

      9, 30, 

      11, 30, 

      31 ), 

    1, DAY( date )

  )

)
  • 1
    I tried with the above formula by modifying date with my input date, as usual its giving error like incorrect parameter type for function 'YEAR()'. Expected Date, received DateTime.... – KS Kumaar Jan 3 '17 at 12:33
  • 1
    Anyplace you see Date in the above formula, you will need to use DATEVALUE( CreatedDate ) as you did in your original formula in your question. CreatedDate is a Date-Time, not a Date Value. – crmprogdev Jan 3 '17 at 12:50
  • I have changed as your instructions, but giving Error: Formula result is data type (Date), incompatible with expected data type (Date/Time). – KS Kumaar Jan 3 '17 at 13:12
  • please note that, i want result in Date-Time format – KS Kumaar Jan 3 '17 at 13:13
  • Just use DateTimeValue(DateResult) to convert it back. It will then display in the user's local time zone. – crmprogdev Jan 3 '17 at 13:19
1

You can use this formula I have handle that case of 31 days here test_dt__c is a test field which you can replace with your own fields.

if( MONTH(DATEVALUE( test_dt__c))> 6,  DATETIMEVALUE(TEXT(YEAR(DATEVALUE( test_dt__c))+1)+"-" +TEXT((MONTH(DATEVALUE( test_dt__c))+6)-12+ ( if(DAY( DATEVALUE( test_dt__c)) == 31 && MONTH(DATEVALUE( test_dt__c)) != 1&& MONTH(DATEVALUE( test_dt__c)) != 7, 1,0)  ))+"-" +TEXT(if(DAY( DATEVALUE( test_dt__c)) == 31 && (MONTH(DATEVALUE( test_dt__c)) == 1 ||  MONTH(DATEVALUE( test_dt__c)) = 7), DAY( DATEVALUE( test_dt__c)),1) )+" "+TEXT(VALUE( MID( TEXT( test_dt__c- 5 ), 12, 2 ) ))+":"+ MID( TEXT( test_dt__c- 5 ), 15, 2 )+":"+ MID( TEXT( test_dt__c- 5 ), 18, 2 ) ), 

DATETIMEVALUE(TEXT(YEAR(DATEVALUE( test_dt__c)) + if(DAY( DATEVALUE( test_dt__c)) == 31 && MONTH(DATEVALUE( test_dt__c)) != 1 && MONTH(DATEVALUE( test_dt__c)) != 7 && MONTH(DATEVALUE( test_dt__c)) == 6, 1,0)   )+"-" +TEXT(MONTH(DATEVALUE( test_dt__c))+ 6  +if(DAY( DATEVALUE( test_dt__c )) == 31 && MONTH(DATEVALUE( test_dt__c)) != 1 && MONTH(DATEVALUE( test_dt__c)) != 7, 1,0))   +"-" +      TEXT(if(DAY( DATEVALUE( test_dt__c )) == 31 && (MONTH(DATEVALUE( test_dt__c)) == 1 ||  MONTH(DATEVALUE( test_dt__c)) = 7), DAY( DATEVALUE( test_dt__c)),1) )   +" "+TEXT(VALUE( MID( TEXT( test_dt__c - 5 ), 12, 2 ) ))+":"+ MID( TEXT( test_dt__c- 5 ), 15, 2 )+":"+ MID( TEXT( test_dt__c- 5 ), 18, 2 ) ))

PS: In this formula I have not handle the case of February and leap year. Which I will leave OP to update.

  • Yes, @Tushar Its not handled with February and leave years..... can you help me in handling with February and leave years..? – KS Kumaar Jan 2 '17 at 11:14
  • @KSKumaar there are many use case which we need to handle. My suggestion you should go with apex trigger. So you can do this in just 2 lines. – Tushar Sharma Jan 2 '17 at 11:33
  • i am unable to get the requirement using formula provided by crmprogdev. Couls you help in this? – KS Kumaar Jan 3 '17 at 12:40

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.