 ShowAll Questionssorted byDate Posted krishna 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.

Regards,
Krishna. Best Answer chosen by krishna chaitanya 35 Alain 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 But ; False because :  2, 28,​ but correct if changed into:   2, 29, but that works only in 2019-2020. https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0

Regards Alain 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 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 But ; False because :  2, 28,​ but correct if changed into:   2, 29, but that works only in 2019-2020. https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0

Regards
This was selected as the best answer krishna 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 35
Hi Alain,
But when i give the date field like june 30,it is throwing an error.can you please help on this.  krishna 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 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) :    ```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