ShowAll Questionssorted byDate Posted
nksf

# Future Date Formula Field

Hi guys I need your help to get future bill date by formula field. It is very urgent.
1st Scenario:
If Billing Frequency Custom Field = Monthly
and Purchase Date Field = 9/01/2015 then this formula field should show Future Bill Date = 10/01/2015
So when Today date is Greater Than(Passes) 10/01/2015 then Future Bill Date should show as 11/01/2015.
Example: Suppose today's Date is 9/22/15 and we have Purchase Date Field = 7/5/15 so Future Bill Date should be showing 10/05/15 and on Date 10/6/15 the Future Bill Date should be showing 11/05/15

2nd Scenario:
If Billing Frequency Custom Field = Quarterly
and Purchase Date Field = 9/01/2015 then this formula field should show Future Bill Date = 12/01/2015
So when Today date is Greater Than(Passes) 12/01/2015 then Future Bill Date should show as 3/01/2015.
Example: Suppose today's Date is 9/22/15 and we have Purchase Date Field = 7/5/15 so Future Bill Date should be showing 10/05/15 and on Date 10/6/15 the Future Bill Date should be showing 1/05/15
hehe I tried doing it..but this is only for the Monthly frequency...

IF(ISPICKVAL(Billing_Frequency__c, "Monthly"),

IF(Purchase_Date__c + 30 >= today(), TEXT(Purchase_Date__c + 30),

IF(Purchase_Date__c + 30 < today(), IF(Day(Purchase_Date__c) > Day(Today()),
TEXT(MONTH(Today())) & "/" & TEXT(Day(Purchase_Date__c))  & "/" & TEXT(Year(Today())),

IF(Day(Purchase_Date__c) < Day(Today()),
TEXT(MONTH(Today())+1) & "/" & TEXT(Day(Purchase_Date__c))  & "/" & TEXT(Year(Today())), null)),null)),

null)

I don't know yet how to use the floor function :D
nksf
Hi Guys I have this Formula but I am exceeding the limit. Can you please tell me how can I get it done within the limit? Also is it correct formula?

IF(Billing_Frequency__c = "M" && Today()> PurchaseDate, DATE(YEAR(PurchaseDate), (MONTH(PurchaseDate)+ FLOOR((Today()- PurchaseDate) / 30.25)+ 1), DAY(PurchaseDate)),
IF(Billing_Frequency__c = "M" && Today() <= PurchaseDate, DATE(YEAR(PurchaseDate), (MONTH(PurchaseDate)+ 1), DAY(PurchaseDate)),
IF(Billing_Frequency__c = "Q" && Today()<= PurchaseDate, DATE(YEAR(PurchaseDate), (MONTH(PurchaseDate)+ 3), DAY(PurchaseDate)),
IF(Billing_Frequency__c = "Q" && Today()> PurchaseDate, DATE(YEAR(PurchaseDate), (MONTH(PurchaseDate)+ FLOOR((Today()- PurchaseDate) / 30.25)+ 3), DAY(PurchaseDate)), NULL))))