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]

You can use the following formulas in the formula tool to check it and change it:

You can see what I did in the attached workflow.

[Kristie_Pires]

Thank you so much @chukleswk! Could you break down for me what the formula is doing at this step:

and left(datetimeadd(datetimetoday(),1,'month'),7) = left([Next Payment],7)
then datetimeadd([Next Payment],-2,'month')

Although it worked on the example, it didn't work on my actual file. So I am curious why is that.

[chukleswk]

datetimeadd(datetimetoday(),1,'month' - This adds 1 month to the current date time of the run

left - we're wanting to pull just the year and month, i.e. 2021-12

We are then comparing it to the same value of the [Next Payment] field

if they are equal (2021-12 = 2021-12) then we do a date add of -2 months off of the [Next Payment] field

What is it outputting or not changing for your actual file?

It might be helpful if you provide an input file (scrubbed of personal data, of course) so that we can see what it's doing.

[chukleswk]

@Kristie_Pires  I'm almost wondering if you're dates are not in the same format? I'm looking for them to be YYYY-mm-dd (2021-12-01) in the formula. the dates that you had as an example are not in that format. That could be part of the issue.

[Kristie_Pires]

Thank you  @chukleswk for breaking down the formula for me 😊 . I also noticed that on your last "ELSE" statement you put [Interest From] instead of [Interest To], was that on purpose?

I think you're right about the format. The input appears to have the same format you're talking about 2020-02-10, but the output changes to 02/10/2020. Is there a way for me to keep it the same before and after the formula tool? If that doesn't work, I'll share a file without any personal/sensitive information.

Thank you for working through this with me.

[chukleswk]

@Kristie_Pires That [Interest From] was an accident. I had copied the formula down and hadn't changed it there. You can change it to [Interest To] and then it'll be correct...sorry about that!

The format on the flow I made doesn't change.... What does your date input look like and what do you want the output to look like? It's not hard to keep it in the same format but may take another step or two.

[Kristie_Pires]

@chukleswk  I included the small part of the workflow. Let me know if works on your end. 

As you can see the information doesn't change when I use your formula. Interest From and Interest To stayed the same.

[chukleswk]

@Kristie_Pires One of the issues that I see is that you're requirement was that if the [Next Payment] is next month (2021-12) is when you want to change and all of your [Next Payment] dates are October. It could be that you don't have any [Next Payment] dates that are in December.

I am now looking into why my dates are changing on my flow (as none of them should be)....

Let me know if this helps!

[Kristie_Pires]

You're so right @chukleswk! I forgot I am testing the workflow with past data. In the future, when I am using the actual data, I will have the [Next Payment] the next month. But this is not the case right now as I am just "building" the workflow still.