Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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.
SteveMo__cSteveMo__c
@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!
jradjrad
I updated your formula as shown below but I'm having issues with December months not calculating dates.  I'm only adding 1 month but this fails on all December dates.  Any idea what I'm missing.

DATETIMEVALUE(DATE((YEAR(MtgPlanner_CRM__Closing_Date__c)+FLOOR(((MONTH(MtgPlanner_CRM__Closing_Date__c)+1)/13))),MONTH(MtgPlanner_CRM__Closing_Date__c)+1,16))

 
SteveMo__cSteveMo__c
The original post is 6+ years old, if I was to write this formula today I would just use the ADDMONTHS function
SteveMo__cSteveMo__c
Like this 
ADDMONTHS( MtgPlanner_CRM__Closing_Date__c , 1)

 
jradjrad
Thanks SteveMo - that was an easy fix and somehow made this more complicated than was necessary.  
Sean RalphSean Ralph

Thanks for a great intel guys.  Related question:

What would the formula be to set a Due Date equal to the 15th of the next month?

E.g. Invoice Date = 1-Nov-21, Due Date should be 15-Nov-21 

Would this be the correct syntax?

DATE(YEAR(blng__Invoice_Date__c)+FLOOR(((MONTH(blng__Invoice_Date__c))/13)),MONTH(blng__Invoice_Date__c),15)
SteveMo__cSteveMo__c
It would be'
  
ADDMONTHS( 
DATE( YEAR( blng__Invoice_Date__c ), MONTH(blng__Invoice_Date__c), 15 ),
 1)

 
SteveMo__cSteveMo__c
That's not the formula that I posted