How to change the Month from December to January of Next year

[Still_Learning_QV]

Hi, it's my first time posting to any forums I am unsure If I am even posting to the right place, but I am stumped and need some direction / help from the community.

I am trying to create a variable where the promotional activity done in December is considered to be part of the next year's activity. While the Day & Year part of the date is getting hardcoded correctly, the Month field is remaining unchanged, which I am having trouble with as for e.g. An original date of '12/05/2022' is getting converted as '12/01/2023' instead of '01/01/2023' a full year ahead of the intended date.

This is my code:

DATE(IF(NUM(YEAR(_Date_))>=2022 and NUM(MONTH(_Date_))=12 and NUM(DAY(_Date_))>=1, '01/01/'&(NUM(YEAR(_Date_))+1),

     IF(NUM(YEAR(_Date_))<2022 and NUM(MONTH(_Date_))=12 and NUM(DAY(_Date_))>=15, '01/01/'& (NUM(YEAR(_Date_))+1),

     NUM(MONTH(_Date_)) & '/01/' & (NUM(YEAR(_Date_))+1) )),'MM/DD/YYYY') AS Activity_Period

I have tried multiple variations to create the Month as I want it to appear, but nothing has worked for me so far.

Any help would be massively appreciated.

[tresesco]

You are at the right place. 🙂

Try like below:

If( InMonth(_Date_, MakeDate(2022,12),0), MakeDate(2023,1))

This will forward all dates from dec 2022 to 1/1/2023.

[Still_Learning_QV]

This isn't working for me. It's returning a blank output. I commented my original expression and swapped this in its place.  

[tresesco]

What you are doing is very string-way. Date calculations are much easier otherwise. Try to share sample date column and expected output agains that. I will try to show you how to script for that.

[Still_Learning_QV]

Hi Thank you for your reply, I am attaching a sample of the dates with this comment.  The reason I am using the string calculation is because I am using the Created Activity Period to create my actual final date variable. 

Thats why Its important for me to get the correct month in this step.

As follows : 

NUM(YEAR(Activity_Period)) AS Year,
NUM(MONTH(Activity_Period)) AS Month,
IF(NUM(MONTH(Activity_Period))=12,NUM(DAY(Activity_Period)),NUM(DAY(_Date_))) AS Day

DATE(MakeDate(Year,Month,Day),'MM/DD/YYYY') AS Date

Thank you for your help so far.

[jcmachado]

You can use the DATE function to construct a date using the three components of year, month and day, and use the MONTH function to get the month part of the date.

Here's an example of how you can modify your code to correctly hardcode the month in the resulting date:

DATE(IF(NUM(YEAR(_Date_))>=2022 and NUM(MONTH(_Date_))=12 and NUM(DAY(_Date_))>=1, 

          DATE(YEAR(_Date_)+1,1,1), 

         IF(NUM(YEAR(_Date_))<2022 and NUM(MONTH(_Date_))=12 and NUM(DAY(_Date_))>=15,

          DATE(YEAR(_Date_)+1,1,1),

          DATE(YEAR(_Date_)+1,MONTH(_Date_),1) 

         )),'MM/DD/YYYY') AS Activity_Period

In the first if statement, the date is hardcoded as '01/01/'&(NUM(YEAR(Date))+1) because you want the month to be January of next year. In the second if statement, the date is hardcoded as '01/01/'& (NUM(YEAR(Date))+1) because you want the month to be January of next year. In the final else statement, the date is hardcoded as DATE(YEAR(Date)+1,MONTH(Date),1) because you want the month to be the same as the original date, but with the year incremented by 1.

This should give you the desired result, with the month being correctly hardcoded as January for promotional activities done in December.

[Still_Learning_QV · Accepted solution]

Hi, Thank you for reply. This is working now. 

Only I need to make small change in the 'then' part of the IF statement. Instead of DATE() I had to use MAKEDATE() to make it work. 

Thank you so much for your help.

DATE(IF(NUM(YEAR(_Date_))>=2022 and NUM(MONTH(_Date_))=12 and NUM(DAY(_Date_))>=1, 

          MAKEDATE(YEAR(_Date_)+1,1,1), 

         IF(NUM(YEAR(_Date_))<2022 and NUM(MONTH(_Date_))=12 and NUM(DAY(_Date_))>=15,

          MAKEDATE(YEAR(_Date_)+1,1,1),

          MAKEDATE(YEAR(_Date_)+1,MONTH(_Date_),1) 

         )),'MM/DD/YYYY') AS Activity_Period