Adding a new column based on the year and month in a date field

[eleee3]

Hi there,

I have a database that looks something like this:

Date	Stage	Stage 1 Date	Stage 2 Date	Stage 3 Date
2019-04-04		01/06/2022	01/10/2022	1/1/2018
2022-06-01		01/06/2022	01/10/2022	1/1/2018
2022-10-08		01/06/2022	01/10/2022	1/1/2018
2020-03-18		01/06/2022	01/10/2022	1/1/2018
2018-09-07		01/06/2022	01/10/2022	1/1/2018
2010-11-09		01/06/2022	01/10/2022	1/1/2018

I want to fill in the "Stage" column based on the month and year in the "Date" column - for example, for dates occurring in June 2022, I want "Stage 1" and in October 2022, I want "Stage 2":

Date	Stage
2019-04-04
2022-06-01	Stage 1
2022-10-08	Stage 2
2020-03-18
2018-09-07
2010-11-09

I've tried an IF formula in Alteryx (outputting to the "Stage" column):

IF DateTimeYear([Date]) = DateTimeYear([Stage 1 Date]) && DateTimeMonth([Date]) = DateTimeMonth([Stage 1 Date]) THEN "Stage 1" ELSE "" ENDIF

The above formula works when I'm assigning stage by year only (i.e., using the formula IF DateTimeYear([Date]) = DateTimeYear([Stage 3 Date]) THEN "Stage 3" ELSE "" ENDIF) but seems to break when I bring in the "Month" requirement (I just get a column full of blanks) - any ideas?

Thanks in advance!

[BRRLL99 · Accepted solution]

Try using this formula

IF DateTimeYear([Date]) = DateTimeYear([Stage 1 Date]) && DateTimeMonth([Date]) = DateTimeMonth([Stage 1 Date]) THEN "Stage 1"
ELSEIF DateTimeYear([Date]) = DateTimeYear([Stage 2 Date]) && DateTimeMonth([Date]) = DateTimeMonth([Stage 2 Date]) THEN "Stage 2"
ELSE ""
ENDIF

Note:

sample data, the Stage 3 date is in the year 2018, while all the other stage dates are in 2022. If this is not a mistake, you will need to add an additional ELSEIF statement for Stage 3 with the correct year and month criteria.

[ShankerV · Accepted solution]

Hi @eleee3 

One way of doing this...

IF DateTimeYear([Date]) = DateTimeYear(datetimeparse([Stage 1 Date],"%d/%m/%y")) AND DateTimeMonth([Date]) = DateTimemonth(datetimeparse([Stage 1 Date],"%d/%m/%y"))
THEN "Stage 1"
ELSEIF DateTimeYear([Date]) = DateTimeYear(datetimeparse([Stage 2 Date],"%d/%m/%y")) AND DateTimeMonth([Date]) = DateTimemonth(datetimeparse([Stage 2 Date],"%d/%m/%y"))
THEN "Stage 2"
ELSE null()
ENDIF

Many thanks

Shanker V

[binuacs · Accepted solution]

@eleee3 Dynamic way of doing this