Calculating 12th BD of the Month

[Masond3]

Team, 

Need some alteryx assistance 

I am extract data on monthly basis (This date still tbc). I currently extract two columns from salesforce (Id and created date) and a create a new column called "Extracted date" 

Our Business updates data on the 12bd of every month, whilst running some logic within alteryx i am encountering issues where the CreatedDate of a record is created after the 12BD of the month, therefore its showing as an issue, when in fact it shouldn't be included in the logic as it wouldn't be touched as part of the process. 

The following month those records that created created after the 12bd of that month would then be updated  to hopefully reflect accurately .

AIM - Flag those records which are created after the 12th Business Day of the Month

The created date is a date-time stores as the following ; (Year, Month, day )

2023-11-17T22:06:34.000+0000
2023-11-22T00:49:41.000+0000
2023-11-28T00:41:37.000+0000
2023-11-25T00:41:32.000+0000

Input 

ID	CreatedDate	Extracted Date
11111	2023-11-17T22:06:34.000+0000	2023-12-01
22222	2023-11-25T00:41:32.000+0000	2023-12-01
33333	2023-11-07T22:33:49.000+0000	2023-12-01
44444	2021-09-07T22:33:49.000+0000	2023-12-01

Expected Outcome 

ID	Created Date	Extracted Date	12 BD	After BD
11111	2023-11-17T22:06:34.000+0000	2023-12-01	16th November	Y
22222	2023-11-25T00:41:32.000+0000	2023-12-01	16th November	Y
33333	2023-11-07T22:33:49.000+0000	2023-12-01	16th November	N
44444	2021-09-07T22:33:49.000+0000	2023-12-01	NA	NA

[Christina_H]

I'm not entirely clear on your logic, but these formulas will get you to the 12th business day of the month.  Hopefully you can use that to get what you need.

FirstOfMonth:

ToDate(Left([CreatedDate ],8)+"01")

12 BD:

IF ToNumber(DateTimeFormat([FirstOfMonth],"%u"))<5
THEN ToDateTime(Left([FirstOfMonth],7)+"-16")
ELSEIF ToNumber(DateTimeFormat([FirstOfMonth],"%u"))<7
THEN ToDateTime(Left([FirstOfMonth],7)+"-18")
ELSEIF ToNumber(DateTimeFormat([FirstOfMonth],"%u"))=7
THEN ToDateTime(Left([FirstOfMonth],7)+"-17")
ELSE Null() ENDIF

[Masond3]

@Christina_H Thank you for your swift response. So all i want to do is flag records (based on created date) which is greater than the 12BD 

Therefore using the formulas you provided, ( i think my next step to get the outcome i need would be applying the following formula 

IF Left([Createddate],8) > [12BD] THEN "Yes" ELSE "F" ENDIF

However as you can see based on the screen shot the new column  "12BD >" is all set to "F"  when i would expect it to be "Yes" 

[Christina_H · Accepted solution]

You need Left(CreatedDate],10) to get the whole date, otherwise you're cutting it off after the month.  I was doing that in the previous formula to switch to first of the month, replacing the day with "01".