DateTimeFormat error

[carterfleming]

I'm trying to achieve three goals with a single Formula tool. First, I'm filling Nulls in one date column with dates from another column:

Then, I'm trying to ensure that the formatting for this column is in proper format. For reference, the existing column that originally had Nulls was not formatted in Alteryx date format, but the column I pulled in to fill the Nulls was already in Date format.

Finally, I want to create a new column that populates the last day of the month for whatever date is in the Billing Period column. For example, if Billing Period = 2022-01-05, I want this new column to populate as 2022-01-31.

My issue is that I keep getting a ton of conversion errors saying that some months are not valid Dates or Time.

I've searched all over the Alteryx discussion boards and haven't been able to find a solution that fits this scenario exactly. Any and all help is greatly appreciated!

[DavidSkaife]

Hi @carterfleming 

In one of your columns you're trying to format you have the date in month-year format, you'll need to add in an additional step to format that into the matching date format:

DateTimeParse([Field1],'%m-%Y')

Parse turns the date into the ISO standard used by Alteryx when you reference the format it's currently in.

[binuacs · Accepted solution]

@carterfleming DateTimeFormat() function works on the DateTime data type and returns a string type, here you are trying to apply the DateTimeFormat function to a string data type, you need to change the Billing Period to Date data type then apply the DateTimeFormat function 

[carterfleming]

So I put a Select tool in front of the Formula and changed the Billing Period to Date type. That helped solved a lot of the issues I had with the Formula, however, a few remain. It seems to go along with what the other response on this post is saying, however, I'm not sure how to harmonize it.

[DavidSkaife]

Hi @carterfleming 

Try adding the following to the formula tool, replacing the first formula in the batch as it looks like the issue is with the non-null fields in Billing Period:

IF [Billing Period] = NULL()
THEN [Date]
ELSE DateTimeParse(Billing Period],'%m-%Y')
ENDIF

[carterfleming]

I tried that out and I'm still getting the same errors:

[binuacs]

@carterfleming Can you screen shot your latest changes in the formula tool here ?

[binuacs]

@carterfleming @Can you remove your second formula and try again ? 

[DavidSkaife · Accepted solution]

Hi @carterfleming 

You're now having the issue with the Select Tool where you changed the data type to Date, and it's not recognising Month-Year as a valid data type. Change it back to what it was originally. DateTimeFormat should still work regardless of it being a Date type or not.

If you're still having issues can you share the workflow at all?

[carterfleming]

So I removed where I changed the Billing Period to Date type in the Select tool prior to the Formula tool and removed the second formula and now it works. Thank you both!