1

I have a Number field that I need to be able to change the value of based on => Today's date. Is this possible with formula fields?

Field 1 = Last_Month_For_Adjustment__c //this is a date field//
Field 2 = Previous_Month_Subscription_Count__c //this is a number field//
Field 3 = Present_Month_Subscription_Count__c //this is a number field//

Required Logic- If Last_Month_For_Adjustment__c is equal to or greater than today, then Previous_Month_Subscription_Count__c would display 0, otherwise it would display value in Present_Month_Subscription_Count__c

Here is the formula that I have tried:

IF(
    TODAY() <=  (Last_Month_For_Adj__c), 
    "0",  
    Present_Month_FT_Subscription_Count__c
)
Improve this question

3 Answers 3

Reset to default
0

Yes, it is possible. The only thing you need to ensure here is that all of the possible results are of the same type as the return type of your formula field (Number, in your case).

The only mistake that I see in your formula is that you put double quotes around 0, making it a string rather than a number.

Fix that, and the formula ought to work

IF(
    /* Just swapping around the arguments to make it more natural to read */
    /* Does not impact the evaluation of the formula */
    Last_Month_For_Adj__c >= TODAY(), 
    /* No quotes around the 0, you want this to be treated as a Number type */
    0,  
    Present_Month_FT_Subscription_Count__c
)
Improve this answer
4
  • Thank you for the response. This should impact only those records that have today's date, or some date in the future. I notice that a record dated for today did not update as expected. Perhaps something else needs to be tweaked?
    – Ron
    Commented Sep 1, 2022 at 14:41
  • @Ron is Last_Month_For_Adj__c a Date, or a DateTime? If it is a DateTime then you may be able to get around that by simply extracting the Date component using DATE(Last_Month_For_Adj__c)
    – Derek F
    Commented Sep 1, 2022 at 14:51
  • it s a date field.
    – Ron
    Commented Sep 1, 2022 at 15:19
  • So, the second half of the solution presented above seems to work fine, where the value in the field get sets to Present_Month_Subscription_Count__c. However, the first part, where the value needs to be set to zero (0) if the date is anytime in the future isn't. I've tried tinkering a bit but haven't found the key. Any insight would be very helpful. Thanks.
    – Ron
    Commented Sep 1, 2022 at 22:06
0

I had to do something similar regarding distrubtion of muni passes. I made it so when you changed the date to today (since they are coming in and getting a physical item, so the date would always be today's date), that it increases the numeric value of a formula field by 1, tracking how many passes they have been given. for me my code was:

IF(LAST_PASS_DISTRIBUTED__c = TODAY(), NUMBER_OF_PASSES_ISSUED_TO_DATE__c + 1, NUMBER_OF_PASSES_ISSUED_TO_DATE__c)

I hope this helps anyone in a similar situtation!

Improve this answer
-1

Ok so looks like I had the wrong return data type, looking for number and received a date.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .