changing a date from a general text format from an excel file to date format

[Ksisterhen]

Hi-

I have a file that brings in the dates like this.  to change them to show as a date format in excel, i would just change the format in the tool bar to date.

How can i change the format to date in alteryx if it comes in like this.

input 

IDCORPCLINIC	DATESTART	DATEEND
11029	42975	45217.0431
5305	40933	45214.03859
3291	39159	45213.01197
9234	44615.63681	45213.01197
2045	38202	45210.02734
11805	43451	45210.02734
1706	38459	45206.01279
2538	39145	45206.01279
12537	42955	45205.01203
1526	37566	45199
11952	44638.66698	45192.15895
2079	41151	45191.03427

want the export to look like 

IDCORPCLINIC	DATESTART	DATEEND
11029	8/28/2017	10/18/2023
5305	1/25/2012	10/15/2023
3291	3/18/2007	10/14/2023
9234	2/23/2022	10/14/2023
2045	8/3/2004	10/11/2023
11805	12/17/2018	10/11/2023
1706	4/17/2005	10/7/2023
2538	3/4/2007	10/7/2023
12537	8/8/2017	10/6/2023
1526	11/6/2002	9/30/2023
11952	3/18/2022	9/23/2023
2079	8/30/2012	9/22/2023

[JosephSerpis]

Hi @Ksisterhen use the Todate formula in combination with Datetimeformat formula to get your dates into MM/DD/YYYY format. If you use a Multi-Field Formula tool you can apply the same function to multiple columns.

[SPetrie]

You can use todate to convert the decimal date to a date/time format and then format it to your liking.

datetimeformat(todate([_CurrentField_]),"%m/%d/%Y")

[Ksisterhen]

I am getting this error when following the above instructions

[SPetrie]

Where there any warnings about numbers being beyond the max or minimum?

Thats the only thing I can think that would cause that issue. If the number was beyond the range of an acceptable decimal format date, it wouldn't convert and stay as a number which would cause the format portion of the formula to give that error.

Are you able to share the data for those columns making the error?

[Ksisterhen]

see attached, also how can i add a column that notes that if there is an order event that contains signed and the event type date was in the past year it should say signed in a new column i.e for order 5

thanks!

[SPetrie · Accepted solution]

The example data all converted with no issues for me. I altered the formula to use the original value if it fails to convert to hopefully make it easier to see where the issue is.

As for a new column, that should be pretty straight forward. Setup a formula to look for items that contain "signed" and have a date that is greater than or equal to today-1year.

The only real option then would be if you wanted it only on the line items that passes that test, or if you want all line items of that order to have the note.

I included both version in the attached workflow.