0

I have a date field (Last_Order_Date__c), in which I am defaulting the value of date to 6 months ahead from today's date.

Default Value for Above Date Field with formula

DATE( 
YEAR(TODAY()) + IF(MONTH(TODAY()) > 6, 1, 0), 
MOD(MONTH(TODAY()) + 6, 12), 
DAY(TODAY()) 
)

Right now it is defaulting the value correctly. Suppose today is 10/29/2016 .It is defaultly setting to this value 4/29/2017. My first question: Is this formula correct for defaulting the date value to the date which is 6months ahead. Will it also consider Months having 31st automatically? FYI I am getting below error if today's date is 10/31/2016 and I am using the above formula for default date.

enter image description here

  • Check out this link for useful date formulas: developer.salesforce.com/docs/… – Nick Cook Oct 31 '16 at 8:47
  • Thanks for the link @NickCook. I am little confused in Date formula. If you could resolve this issue it could be greatfully for me. – Dinesh Oct 31 '16 at 8:52
  • 2
    Your question is really unclear. What exactly is the problem? Are you unsure of your default value, or your validation rule? You also need to consider what should happen it the future month doesn't have that many days. What should happen? what's Dec 31 + 6 months? June 30, or July 1? – Nick Cook Oct 31 '16 at 9:34
  • Why are you defaulting 'Last_Order_Date__c' to be 6 month ahead from today? It will always show 6 month ahead date event if you open the record after a month. You need to reference a field, i.e. 'Created Date', in your formula field so that way your calculated date will be fixed to a particular value 6 months in future. – Mahmood Oct 31 '16 at 10:23
  • @Mahmood 'Last_Order_Date__c' is a Date field.When I open the record I am defaulting to 6month ahead date so that user don"t need to select.How will be the formula if we uses CreatedDate inspite of Today().Any Idea? – Dinesh Oct 31 '16 at 13:04
1

As per the Sample Date Formulas, specifically the Adding Days, Months, and Years to a Date section:

DATE(
  YEAR( date ) + FLOOR( ( MONTH ( date ) + 6 - 1 ) / 12 ),
  MOD( MONTH ( date ) + 6 - 1 + 
    IF( DAY ( date ) > CASE( MOD( MONTH( date ) + 6 - 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 ) + 6 - 1, 12 ) + 1,
      2, 28, 
      4, 30, 
      6, 30, 
      9, 30, 
      11, 30, 
      31 ), 
    1, DAY( date )
  )
)

Where date should be replaced with the date you want. i.e. today()

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.
  • @Dinesh; you might want to alter the above to coerce 10/31/16 -> 4/30/17 etc for 31 -> 30 day mos. This is a business use case decision – cropredy Oct 31 '16 at 21: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.