BB Date returns Null for Date Field and an extra Null column

I have four different date fields that I am trying to format into a standard date format using the BB Date macro. It is working for three of the fields just fine but the fourth field always returns a Null value in the field and returns a new Null column. The format of the date string coming in is %m/%d/%Y h:mm an example date is 9/30/2008 0:00. I am using the BB Date macro since the dates can come in multiple formats for this field depending on the file entered. A few of the other fields that are working are using the exact same input format and are being converted fine.

Looking through the macro and sub macros I do not see where the Null column is created but there is a lot of levels within this macro along with a lot of tools being used. Has anyone familiar with the BB Date seen similar behavior and have any hints on how to solve this?

Date Time

Accepted answers

DanM

@jdodd,

This tool was created by one of the Alteryx employees and is technically not a macro that is supported by Alteryx, so no update will happen with this tool unless the creator does so on their own. You are welcome to reach out to the creator and ask them if they have plans to update the tool. Otherwise, you are correct that you would have to create a new tool.

Dan

All comments

ivoller

AM or PM in the date field? See https://community.alteryx.com/t5/Engine-Works-Blog/Introducing-BB-Date-Date-Parsing-for-the-Rest-of-Us/ba-p/24370

Iain

jdodd

Thanks for the suggestion but there were no AM or PM in the date field. I have read that article and look at the various limitations like only having a two digit year but none of those apply to this scenario. In fact, I have another date field with the exact same format that does work so I am baffled why one would and the other wouldn't.

BenMoss

Hi!

I'm not familiar with the macro but just gave it a try and I seem to get a similar problem.

When I add a second date it no longer generates the 2nd NULL field, and my data in the original field has been adjusted accordingly (though after converting it to a 'date' type this row then NULLs out).

It would appear that this type is not supported by the marco. Could you pass over an example that you say is in the same format but works?

Ben

MarqueeCrew

Could we go old school on this with the following formula:

DateTimeParse([Field1],"%m/%d/%Y %H:%M")

This should work for dates in the format that you provided as an example.

Cheers,

Mark

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703