Year number out of Range - Trying to format date

[henrymk5]

I'm trying to format date  3/31/2020 11:44 AM EDT to 2020-03-31 format. I have tried multiple different methods but I'm still continuing to receive the error message "Year number is out of range"

Does anyone have any ideas on how I can properly format the above date.

Thank you in advance!

[fmvizcaino]

Hi @henrymk5 ,

In this case, you need to use the following custom format in the datetime tool.

MM/dd/yyyy %I:%M %P

You can find more here. https://help.alteryx.com/2020.1/Reference/Functions.htm

Best,

Fernando Vizcaino

[henrymk5]

Sorry, maybe my question was not clear.

I want to go from 3/31/2020 11:44 AM EDT (before) and update to be 2020-03-31 (after).

None of the date/time functions seem to be working as it gives me an error.

[fmvizcaino]

Hi @henrymk5 

Workflow attached. Take a look at both examples, including time and not including as well.

Best,

Fernando Vizcaino

[neilgallen]

you can do this with a formula tool and the below formula:

datetimeparse([field],"%m/%d/%Y")

this will take the field and create the standard date format. 

[henrymk5]

Hi Fernando,

Thanks for the reply.

Did you update anything with your format. Please take a look at my dataset and my results as the configurations matches yours.

I'm not sure why I'm still receiving a cannot convert error.

For this example I'm giving you a small sample of a bigger excel input

[henrymk5]

My attachments did not work 🙂

  

[fmvizcaino · Accepted solution]

Hi @henrymk5 ,

You are not using the correct format, you need to select the format from the incoming field, which is MM/dd/yyyy and it will be converted to yyyy-mm-dd automatically

[henrymk5]

Amazing!! Thanks for the good catch and solution @fmvizcaino !!!

[henrymk5]

This solution worked as well! Thank you 🙂