+ Start a Discussion
Payal KamblePayal Kamble 

best way to auto populate a field based on elapsed date

Hello Everyone,
I am new to salesforce and need insights on how to approach the below problem:
I need to calculate a quarterly "Next" due date based on a give date field. For example, if the original date field is 10/4/16, then the next due date would be 1/2/17 (just adding 90 days). This needs to auto populate everytime(may be I can compare with today's date to do the next calculations)
There may not be any update on the record that would trigger this action.
What is the best way to do this? I looked at formula fields, but my problem is that the original date could be more than 365 day back, and I am not sure how to proceed with the calculations in that case. I looked at tiggers, but looks its executed based on record creation or updates.

Thank you in advance!
Best Answer chosen by Payal Kamble
Malni Chandrasekaran 2Malni Chandrasekaran 2
Payal,
Thought so... :)

Try this formula  - Replace SLAExpirationDate__c with your policy date.

IF(
MOD(((Year(Today()) - Year(SLAExpirationDate__c))   *  12 )+ (Month(Today()) - Month(SLAExpirationDate__c)), 3) = 0 && Day(Today()) < Day(SLAExpirationDate__c),

 Date(Year(Today()), Month(Today()), Day(SLAExpirationDate__c)) ,


Date(
Year(Today()), 
Month(Today()) + 3 - MOD(((Year(Today()) - Year(SLAExpirationDate__c))   *  12 )+ (Month(Today()) - Month(SLAExpirationDate__c)), 3),  
Day(SLAExpirationDate__c)
)
)

All Answers

Malni Chandrasekaran 2Malni Chandrasekaran 2
Payal,
I am trying to understand this part 'I looked at formula fields, but my problem is that the original date could be more than 365 day back, and I am not sure how to proceed with the calculations in that case.'

should not add 90 days if it is more than a year old? if you explain 'the calculations in that case' I can try to find a solution.
 
Payal KamblePayal Kamble
Hi Maini,
So the original date field does not change. so if this date is 10/4/16. The new field "due date" initially would be  10/4/16+90 days= 1/2/17. This should again be refreshed when the due date is elapsed, so it would again be calculated as  1/2/17+90 = 4/2/2017 and so on..
I don't know how to achieve this without doing the manual calulations till the current year(2017 in this case).
Does that make sense?
Payal KamblePayal Kamble
Hi Maini,
So the original date field does not change. so if this date is 10/4/16. The new field "due date" initially would be  10/4/16+90 days= 1/2/17. This should again be refreshed when the due date is elapsed, so it would again be calculated as  1/2/17+90 = 4/2/2017 and so on..
I don't know how to achieve this without doing the manual calulations till the current year(2017 in this case).
Does that make sense?
Malni Chandrasekaran 2Malni Chandrasekaran 2
Payal,
Try this formula in formula field and let me know

TODAY()  + (90 - MOD(TODAY() -   DATEVALUE(OriginalDate),90))
Malni Chandrasekaran 2Malni Chandrasekaran 2
Payal,
Did this work for you? I have tested in my org and it worked fine. Let me know whatever is the result
Payal KamblePayal Kamble

Hi Malni,
No, it did not work, But I tweaked it a bit to give me date which is more closer to the actual date:


IF(DATE( YEAR( TODAY() ) , MONTH( Policy_Date__c ) , DAY(Policy_Date__c))> TODAY() , DATE( YEAR( TODAY() ) , MONTH( Policy_Date__c ) , DAY(Policy_Date__c)), DATE( YEAR( TODAY() ) , MONTH( Policy_Date__c ) , DAY(Policy_Date__c))+90)

For policy_date= 5/18/2016, the date I got from your formula is 11/9/2017.
The expected outcome is 8/18/2017
With the modified formula, I got this date: 8/16/2017

Is there any other way?

Thank you again!

Malni Chandrasekaran 2Malni Chandrasekaran 2
Payal,
For the date you have given

May 18 2016 +90 = Aug 16 +90 = Nov 14 + 90 = Feb 12 + 90 = May 13 + 90 = Aug 11 +90 = Nov 9

Aug 11 already elapsed so Nov 9

I can help you if you can tell me the exact requirement with example dates :).



 
Payal KamblePayal Kamble
That does make sense!
These are premium due dates for insurance policies and the payment could be montly, annual, semi-annual or quarterly.
So for a quarterly payment mode, I need to figure out when is the next payment due.
From the insurance provider portal, for the policy date: 5/18/2016, this is the next due date I got: 8/18/2017.
I am not entirely sure how they are calculating the number of days in each quarter.
I will check and pull out a few more dates to see if that helps.
 
Payal KamblePayal Kamble
Hi Malni,
I checked with the people in my team, and so we are going to keep the day same as the policy date, but need to change the number of months to keep adding 3.
Malni Chandrasekaran 2Malni Chandrasekaran 2
Payal,
Thought so... :)

Try this formula  - Replace SLAExpirationDate__c with your policy date.

IF(
MOD(((Year(Today()) - Year(SLAExpirationDate__c))   *  12 )+ (Month(Today()) - Month(SLAExpirationDate__c)), 3) = 0 && Day(Today()) < Day(SLAExpirationDate__c),

 Date(Year(Today()), Month(Today()), Day(SLAExpirationDate__c)) ,


Date(
Year(Today()), 
Month(Today()) + 3 - MOD(((Year(Today()) - Year(SLAExpirationDate__c))   *  12 )+ (Month(Today()) - Month(SLAExpirationDate__c)), 3),  
Day(SLAExpirationDate__c)
)
)
This was selected as the best answer
Payal KamblePayal Kamble
Hi Malni,
I don't think we save SLAExpirationDate__c in the system. I only have the policy date to work with.
Malni Chandrasekaran 2Malni Chandrasekaran 2
Payal,
As I have mentioned Replace SLAExpirationDate__c with your policy date.

I just used SLAExpirationDate__c for my testing in my org.
Payal KamblePayal Kamble
Oh sorry! Got a bit confused. Let me try that now :)
Payal KamblePayal Kamble
Hi Malni,
Your formula works perfectly! Thank you so much !
But for some reason, in salesforce, it is not working for the month of July.
I verified it against two dates in july:  7/24/2012 & 7/4/2011
The new field is blank. There is no issue with the formula, if I do the calculations in the formula manually, it give me correct results, but its not working in salesforce.
Do you have any idea why?

Thanks again!