+ Start a Discussion
JessicatyeJessicatye 

Date Field 3 Months Out on 15th Day

I have a standard date field that I would like to use in a formula field to timestamp 3 months out on the 15th of the month. For example. if the date field was in 6/24/15 I want the formula date field to read 9/15/15 (regardless of the number of days in a month).

Any help would be greatly appreciated!
James LoghryJames Loghry
I believe you want something like this:
 
DATE(YEAR(TODAY())+FLOOR(((MONTH(TODAY())+3)/13)),MONTH(TODAY())+3,15)

This formula returns a Date type (You can convert it to text if you need to).  The FLOOR(...) function handles the situations where you get into October, November, or December and need to wrap into the next year by using some simple math calculations.
SteveMoSteveMo
@James Loghry I'm totally stealing the FLOOR thing, much cleaner than my usual "rubbing two sticks together to make fire" approach:
DATE( 
YEAR( Date_1__c ) + 
IF(MONTH(Date_1__c) > 9, 1, 0), 
MONTH(Date_1__c) + 
IF(MONTH(Date_1__c) < 10, 3, -9), 
15)

 
JessicatyeJessicatye
This was perfect. Thank you so much!