Year Change

[ArijitRoy]

Hi All, Need help on this.

We are in 2022 hence, as per the below chart, I want after 2023, all the year to be changed to 2023.

Once we will be in 2023, all the future year after 2024 should get changed to 2024 and so on.

How it is possible?

Date	Want
2027-2-21	2023-2-21
2026-5-24	2023-5-24
2025-1-15	2023-1-15
2024-5-19	2023-5-19
2023-4-12	2023-4-12
2022-2-19	2022-2-19
2022-1-11	2022-1-11

[atcodedog05]

_

[OllieClarke]

Hey @ArijitRoy try this formula:

IF DATETIMEYEAR([Date])>DATETIMEYEAR(DATETIMETODAY()) 
THEN
TOSTRING(DATETIMEYEAR(DATETIMEADD(DATETIMETODAY(),1,'year')))+SUBSTRING([Date],4)
ELSE [Date]
ENDIF

This will put all future dates (i.e. >= next year) into next year's year

Hope that helps,

Ollie

[rohitashsharma]

Hi,

The formula doesn't seem to be dynamic, please advise if the data changes dynamically every time example what if when the dates are getting changed every time the data is pulled suppose next year we need to change 2026 and 2027 to 2024.

Thanks,

[rohitashsharma]

Hi Ollie,

Since we are new to the Alteryx tool and never used Tostring and SubString first, please explain the use of these and second if the formula will work if the dates get changed dynamically.

Thanks,

[atcodedog05]

Nicely done @OllieClarke 😀👍

[atcodedog05]

Hi @rohitashsharma & @ArijitRoy 

Answering in @OllieClarke absence. His post deserves the credits if it works please mark his post as a solution.

@OllieClarke  workflow should ideally work here I have created a test column [today] where you can change value and can check output.

Workflow:

ToString() is used to convert number to string

SubString([Date],4) fetches string from 4th index which will return "-02-21"

String function reference document: https://help.alteryx.com/20213/designer/string-functions 

Hope this helps : )

[OllieClarke · Accepted solution]

Hi @rohitashsharma @ArijitRoy 

Sorry, I should have explained my formula:

IF DATETIMEYEAR([Date])>DATETIMEYEAR(DATETIMETODAY()) 

This line compares the year of the date column to the current year. If the date is in the future then it applies the next bit:

TOSTRING(
DATETIMEYEAR(
DATETIMEADD(
DATETIMETODAY()
,1,'year')
)
)
+SUBSTRING([Date],4)

I've broken this out to make it a bit easier to read. 

The first thing that happens is we add 1 year to the current date. We then find the year of that date (so currently that would be 2023). Having done this we convert this number to a string so we can append the month and day from the [Date] column to it (as @atcodedog05 explained).

Hope that makes things clearer

Ollie