Datetime string minus one month

[wenyg]

Hi, how to minus one month from "effectivedate format" column and then compare them using "reversing?" column?

Should I add in one more column?

Or is there a better way to narrow down into one formula?

Thanks

[ShankerV · Accepted solution]

Hi @wenyg 

To answer your first question, you can datetimeadd([Fieldname],-1,"months") to minus one month from the date.

[FinnCharlton · Accepted solution]

Hi @wenyg , 

To minus one month from your effective date column you can replace the formula with

datetimeformat(

datetimeadd(

datetimeparse(tostring([effectivedate]),"%Y-%m-%d"),

-1,"month"),

"%Y-%m")

[ShankerV · Accepted solution]

Hi @wenyg 

You can do it in one formula, but for this both columns should be in date datatype.

if [Dateformat]>datetimeadd([Effectivedate format],-1,"months") then "No"
elseif [Dateformat] = datetimeadd([Effectivedate format],-1,"months") then "YES"
ELSE "ignore"
endif

[binuacs · Accepted solution]

@wenyg One way of doing this

[ShankerV · Accepted solution]

Hi @wenyg 

Hope you are expecting the result like this.

Input was:

IF DateTimeparse([Dateformat],'%Y-%m')> datetimeadd(DateTimeparse([Effectivedate format],'%Y-%m'),-1,"months")
THEN "No"
ELSEIF DateTimeparse([Dateformat],'%Y-%m')=datetimeformat(datetimeadd(DateTimeparse([Effectivedate format],'%Y-%m'),-1,"months"),"%Y-%m-%d")
THEN "YES"
ELSE "Ignore"
ENDIF

Many thanks

Shanker V

[wenyg]

Hi Sanker, the formula you write does work for the original data you put, but does not apply when I switch to 2023 - 01 data. The logic I want to apply here is

1) if dateformat is the same as effective date format less than one month then yes - but this equal clause doesn't seem to work.

2) if dateformat is earlier than effective date format less than one month then no,

for example, 11-2022 should be the recognised as "No" if the effective date is 2023-01, and 12-2022 should be recognised as "Yes". Can you take a look if there is anything wrong with my formula?

[ShankerV · Accepted solution]

Hi @wenyg 

Please find the modified formula to get the expected output.

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @wenyg 

I have created the formula as per the initial screenshot attached,

Now corrected the below to get the desired output.

Many thanks

Shanker V

[wenyg]

Thanks, this works!