You need to sign in to do that
Don't have an account?

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
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
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
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))))