Build Formula with Dates

[Kristie_Pires]

Hi everybody,

I need help building a formula for the following situation:

[Interest From] and [Interest To] are both dates in the past (let's say 02/10/2020 and 02/10/2020) -- yes, they have the same date.

And the column for [Next Payment] is the following month (let's say 12/10/2021).

So what I want to do every time this situation appears (Interest From and Interest To) in the past AND Next Payment the following month:

I want to change the month of the [Interest From] to 2 months before the [Next Payment] date (in this case, it would be October) and the [Interest To] one month prior to the [Next Payment] date (in this case, it would be November).

These are all date fields. 

Thank you so much for your help!

[chukleswk · Accepted solution]

If you want to do it using the current data you can change the calculation for left(datetimeadd(datetimetoday(),1,'month'),7) to left(datetimeadd(datetimetoday(),-1,'month'),7) and that will look for all of the Payment Dates in October instead of December. Just remember to change it back when you're done testing.

If this solution works for you, please make sure that you mark it as accepted so that other's can find it if they come across the same issue!

[Kristie_Pires]

@chukleswk Thank you so much!!! 😊 It works now! 

Have a great weekend!