Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
nksfnksf 

Date Formula

Hi Guys I need your help with this Date Formula Field. It is very urgent
Here is my Formula
IF(Billing_Frequency__c = "M" && Today()> PurchaseDate && Today()< UsageEndDate, DATE(IF(Month(PurchaseDate)= 12, YEAR(Today())+1,YEAR(Today())), (MONTH(PurchaseDate)+ FLOOR((Today()- PurchaseDate) / 31)+ 1), DAY(PurchaseDate)), 
IF(Billing_Frequency__c = "M" && Today() <= PurchaseDate && Today()< UsageEndDate, PurchaseDate, NULL))

This Formula is working fine if Year of PurchaseDate is 2015 or above but if Year of PurchaseDate is 2014 then I am getting #Error! value.
Can you please let me know what I am doing wrong? Once again it is very urgent. Thanks in advance!!!
 
Alexander TsitsuraAlexander Tsitsura
Hi nksf

Problem with next line 
(MONTH(PurchaseDate)+ FLOOR((Today()- PurchaseDate) / 31)+ 1)

when you count for 2014 
(Today()- PurchaseDate) / 31)
result > 12, but this is incorect. Month should be from 1 to 12.

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you.

Thanks,
Alex

 
nksfnksf
Thanks Alex! Can you please help me with correct formula? 
Alexander TsitsuraAlexander Tsitsura
Yes, of course. Can you please explaine business logic for me?

Thanks,
Alex
Alexander TsitsuraAlexander Tsitsura
or can you write some examples of expected date(PurchaseDate is 2014)?
Alexander TsitsuraAlexander Tsitsura
I do not understand your business logic, by try this formula
  
IF(Billing_Frequency__c = "M" && Today()> PurchaseDate && Today()< UsageEndDate, 
DATE(IF(Month(PurchaseDate)= 12, YEAR(Today())+1,YEAR(Today())) + ((MONTH(PurchaseDate)+ FLOOR((Today()- PurchaseDate) / 31)) / 12), 
IF(MOD((MONTH(PurchaseDate)+ FLOOR((Today()- PurchaseDate) / 31)+ 1), 12) = 0,
12,
MOD((MONTH(PurchaseDate)+ FLOOR((Today()- PurchaseDate) / 31)+ 1), 12)
), 
DAY(PurchaseDate)), 
IF(Billing_Frequency__c = "M" && Today() <= PurchaseDate && Today()< UsageEndDate, PurchaseDate, NULL))

 
nksfnksf
Hi Alex,For my requirement these are the 3 fields we need to useBilling Frequency  Formula FieldPurchaseDate      Date FieldUsageEndDate     Date Field My requirement is if Billing Frequency = Monthly and Today () is greater than PurchaseDate and Less than UsageEndDate than show future bill date.For example  if PurchaseDate= 11/2/2015 and UsageEndDate= 6/6/2016 and Today () Date is 10/2/2015 then Future Bill Date should be showing 10/2/2015. Tomorrow when Date be 10/3/2015 Future Bill Date should be showing 11/3/2015  (it should add 1 month) If Today () Date is less than PurchaseDate than it should only be showing PurchaseDate.  Another tough part is if Billing Frequency is Quarterly then using same conditions above we need to add 3 months to get Future Bill Date
Alexander TsitsuraAlexander Tsitsura
Hi nksf

try this formula 
IF(Billing_Frequency__c  = "M" && Today() > PurchaseDate && Today() < UsageEndDate,
   DATE(
     YEAR(Today()) + (MONTH(PurchaseDate) + 1) / 12, 
     MOD(MONTH(PurchaseDate), 12) + 1, 
     DAY(PurchaseDate)
   ),
   
   IF(Billing_Frequency__c  = "M" && Today() <= PurchaseDate && Today() < UsageEndDate, PurchaseDate, NULL)
)

And other formula with Quarterly type
  
IF(Billing_Frequency__c = "M" && Today() > PurchaseDate && Today() < UsageEndDate,
   DATE(
     YEAR(Today()) + (MONTH(PurchaseDate) + 1) / 12, 
     MOD(MONTH(PurchaseDate), 12) + 1, 
     DAY(PurchaseDate)
   ),
   
   IF(Billing_Frequency__c = "M" && Today() <= PurchaseDate && Today() < UsageEndDate, PurchaseDate, 
      IF(Billing_Frequency__c = "Q" && Today() > PurchaseDate && Today() < UsageEndDate,
         DATE(
           YEAR(Today()) + (MONTH(PurchaseDate) + 3) / 12, 
           MOD(MONTH(PurchaseDate) + 2, 12) + 1, 
           DAY(PurchaseDate)
         ),
   
         IF(Billing_Frequency__c = "Q" && Today() <= PurchaseDate && Today() < UsageEndDate, PurchaseDate, NULL)
      )
   )
)

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you.

Thanks,
Alex
nksfnksf
Hi Alex Thanks for your help but I am not getting the required values let me just explain hte requirement once again

We have Formula Field Billing Frequency and we have PurchaseDate Standard Date Field and UsageEndDate Standard Date Field. 
I need to create a Formula Field to Calculate the Future Bill Date
Scenario:
If we have Billing_Frequency__c = Monthly and PurchaseDate = 8/1/15 and EndDate = 12/1/15 
Suppose Today() Date is 9/23/15
then if Today() Date is > PurchaseDate and < EndDate then this Formula Field should show Future Bill Date as 10/1/15 
so when Today() Date be 10/2/15 then this Formula Field should show Future Date as 11/1/15

If Today() Date is <= PurachaseDate then this Formula Field show just show PurchaseDate
In case Today()Date is < EndDate then this Field should be Blank.
and
If we have Billing_Frequency__c = Quarterly and PurchaseDate = 05/1/15 and EndDate = 12/1/16 
Suppose Today() Date is 10/05/15
then if Today() Date is > PurchaseDate and < EndDate then this Formula Field should show Future Bill Date as 11/1/15 (if we add 3 months to 5/1/15 then future bill date be 8/1/15 but 8/1/15 is still past date of today's date so we are gonna add 3 more months to 8/1/15 then we will get the correct future bill date which will be 11/1/15. Same think when today's date will pass 11/1/15 mean when today() date be 11/2/15 then Future Bill Date should be showing 2/1/15. (Just added 3 more months to 11/1/15). 

If Today() Date is <= PurachaseDate then this Formula Field show just show PurchaseDate
In case Today()Date is < EndDate then this Field should be Blank.

Please let me know if it is still not clear.