Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
krishna chaitanya 35krishna chaitanya 35 

calculate the given date minus 6 months

Hi All,
I have field on opportunity which shows contract expiration date ,but a reminder should be sent to opportunity owner before 6 months .I need a  formula to calculate today()-6 months, i tried Today()-180 but it misses some days.

Thanks for your help in advance!........
Regards,
Krishna.
Best Answer chosen by krishna chaitanya 35
Alain CabonAlain Cabon
Problem of leap year for February: 29 days instead of 28.

The next leap day is February 29, 2020.
The last Leap Day was on February 29, 2016.

This formula does the following:
  1. 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. So this formula is not correct for a leap year.
  2. 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.
  3. Otherwise, it returns the correct date in the future month.
  • February (2)  28 days
  • April (4), June (6), September (9), November (11) 30 days 
  • Else 31 days.
OK: if not a leap year
User-added image
But ;
User-added image
False because :  2, 28,​ but correct if changed into:   2, 29, but that works only in 2019-2020.

User-added image

https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0

Regards

All Answers

Alain CabonAlain Cabon
Hi,

Your formula is a estimate, it doesn’t return an exact date. 

Adding Days, Months, and Years to a Date: Adding months to a date is slightly more complicated as months vary in length and the cycle of months restart with each year. 

This example formula adds six 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.
  
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 )
  )
)

https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0

Regards
Alain
Alain CabonAlain Cabon
Problem of leap year for February: 29 days instead of 28.

The next leap day is February 29, 2020.
The last Leap Day was on February 29, 2016.

This formula does the following:
  1. 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. So this formula is not correct for a leap year.
  2. 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.
  3. Otherwise, it returns the correct date in the future month.
  • February (2)  28 days
  • April (4), June (6), September (9), November (11) 30 days 
  • Else 31 days.
OK: if not a leap year
User-added image
But ;
User-added image
False because :  2, 28,​ but correct if changed into:   2, 29, but that works only in 2019-2020.

User-added image

https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0

Regards
This was selected as the best answer
krishna chaitanya 35krishna chaitanya 35
Hi Alain,
Thank you for your quick support ,i will try the same and let you know the result.Mean while i tried with another formula
 IF 
( 
MONTH(Contract_Expiration_Date__c) < 7 
, 
DATE 
( 
YEAR(Contract_Expiration_Date__c) - 1, 
MONTH(Contract_Expiration_Date__c) - 6 + 12, 
DAY(Contract_Expiration_Date__c) 
) 
, 
DATE 
( 
YEAR(Contract_Expiration_Date__c), 
MONTH(Contract_Expiration_Date__c) - 6, 
DAY(Contract_Expiration_Date__c) 
) 
)
Regards,
Krishna.
krishna chaitanya 35krishna chaitanya 35
Hi Alain,
But when i give the date field like june 30,it is throwing an error.can you please help on this.
User-added image
krishna chaitanya 35krishna chaitanya 35
Hi Alain,
I prefer to chose previous date when the 31 doesn't exist.But also for the 30th month it is showing the above error.Can you please modify the formula according to these two conditions .Can you please help me on this.

Regards,
Krishna.
Alain CabonAlain Cabon
Hello Krishna,

Here is the good solution (including the leap years):

Chun Wu - 4 years ago This is the version of subtracting months:
https://success.salesforce.com/ideaview?id=08730000000BrQ2AAK

test_date - test number (of month) :

User-added image
User-added image

User-added image

User-added image

  
DATE (
 
    /*YEAR*/
    YEAR (test_date__c) - FLOOR(test_number__c / 12) - IF (MONTH (test_date__c) - MOD(test_number__c, 12) > 0, 0, 1),
        
    /*MONTH*/ 
    IF (MONTH (test_date__c) - MOD (test_number__c, 12) > 0, MONTH (test_date__c) - MOD (test_number__c, 12), MONTH (test_date__c) - MOD (test_number__c, 12) + 12),
 
    /*DAY*/ 
    MIN (DAY (test_date__c), 
        CASE (
        /* Month */
        IF (MONTH (test_date__c) - MOD (test_number__c, 12) > 0, MONTH (test_date__c) - MOD (test_number__c, 12), MONTH (test_date__c) - MOD (test_number__c, 12) + 12), 9, 30, 4, 30, 6, 30, 11, 30, 2, 
        /* return max days for February dependent on if end date is leap year */
        IF (MOD (YEAR (test_date__c) - FLOOR(test_number__c / 12) - IF (MONTH (test_date__c) - MOD(test_number__c, 12) > 0, 0, 1), 400) = 0 || (MOD (YEAR (test_date__c) - FLOOR(test_number__c / 12) - IF (MONTH (test_date__c) - MOD(test_number__c, 12) > 0, 0, 1) ,4) = 0 && MOD (YEAR (test_date__c) - FLOOR(test_number__c / 12) - IF (MONTH (test_date__c) - MOD(test_number__c, 12) > 0, 0, 1) ,100) <> 0 ), 29, 28), 31)
    )
)/* End of Date function */

Regards