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

Improve this question
8
  • Check out this link for useful date formulas: developer.salesforce.com/docs/…
    – Nick C
    Commented Oct 31, 2016 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.
    – user3189976
    Commented Oct 31, 2016 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 C
    Commented Oct 31, 2016 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
    Commented Oct 31, 2016 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?
    – user3189976
    Commented Oct 31, 2016 at 13:04

1 Answer 1

Reset to default
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.
Improve this answer
1
  • @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
    Commented Oct 31, 2016 at 21:40

You must log in to answer this question.

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