I have 3 fields Start Date (Date Type), Term Length (Number) & End Date (Date Type). The user enters a start date and the contract term length in months, I need to calculate the end date.
For e.g. *09/12/2012 + 5 = 02/12/2013* -- -- NOT+ -- _09/17/2012_.
Basically add the number of months to the date and calculate the end date, but the default in CRMOD is adding the number as no. of days not month.
Is there any way I can do this using workflows. (I need the data displayed on the layout).
I thought of using the "Month" function to extract the value and add the no. of months, but it threw an error that it cannot work with 1 parameter.... I assume it is reporting only function and will not work in workflows. Is there any way/logic by using Julian Day/Month or some other function to do this calculation?
If not possible using workflows, as a last resort is it doable using reports?
Try this :
Create a Workflow and use the below expression on the field you want to update
30 is the number of days in a month, this is a simple approach. If you want to use the actual number of months (which I think is your requirement) from that start date to the end date based on the number of months selected in Term Length, you will have to so some more If's and else, use the IIf function to validate this.
Ex: IIf(Month value of Start date is 1/January and the number of months selected in term length is 5 then you will have to build your logic around that to get number of months in each of those 5 months and use the sum of that in the code above.
Thanks for the reply with the 2 solutions.
I thought of multiplying months*30, but is that how oracle treats the default month calculations in the system (if any).
Wouldn't it show a discrepancy in the report where I can use TIMESTAMPADD(SQL_TSI_MONTH, no. of months, start date).
Will your formula and this report show the same dates? (Will try and update the answer)
2nd solution might work but we are supposed to have a limit of 84 months in that field and using the IIF condition, does the workflow allow for that many conditions (without or with breaking it up into many workflows)
will try to see if the 1st option works.