My worksheet is converting my unit numbers to dates. I cant seem to prevent it. Is there some syntex that would force my unit numbers to remain txt to prevent conversion to date or electrical/scientific. Unit # Tenant 1-1817Ashleigh Allen1-1818Sununda BurrellJan-01Lashion AlexanderJan-02Catrina HendersonJan-03Quadell DensonJan-04David PiperJan-05Pablo HaynesJan-06Lawrence MarshallJan-07Stephanie FrenchJan-08Brenda DavisJan-11Nicole RansomJan-12Jordan WilliamsJan-13Kentella WilsonJan-14Laketra HillJan-15Quintracious EspreeJan-16Tyrone GallowJan-17Alysha YoungJan-18Luis Hernandez Rodriguez2-102Rony Reyes2-103Lashondra Mathis2-104Senetria Russell

Number or Text Field Converting to Date

My worksheet is converting my unit numbers to dates. I cant seem to prevent it. Is there some syntex that would force my unit numbers to remain txt to prevent conversion to date or electrical/scientific.

Unit # Tenant

1-1817	Ashleigh Allen
1-1818	Sununda Burrell
Jan-01	Lashion Alexander
Jan-02	Catrina Henderson
Jan-03	Quadell Denson
Jan-04	David Piper
Jan-05	Pablo Haynes
Jan-06	Lawrence Marshall
Jan-07	Stephanie French
Jan-08	Brenda Davis
Jan-11	Nicole Ransom
Jan-12	Jordan Williams
Jan-13	Kentella Wilson
Jan-14	Laketra Hill
Jan-15	Quintracious Espree
Jan-16	Tyrone Gallow
Jan-17	Alysha Young
Jan-18	Luis Hernandez Rodriguez
2-102	Rony Reyes
2-103	Lashondra Mathis
2-104	Senetria Russell

Workflow

Designer Cloud

Regex

Expression

Accepted answers

binu_acs

@sslattery17 This is not an alteryx issue, the issue is with the excel where the data 1-1900 is converting a date Jan-01 by default, this has to be fixed in the excel, may be this should be fixed in the client side before exporting to excel or another option have the client to upload as csv instead of excel

All comments

binu_acs

@sslattery17 Wjat are the actual values of these date fields

Qiu

@sslattery17
When you say your "Worksheet" you mean Excel sheet?
Can you try to define the Column to be Text then try to import to Alteryx?

sslattery17

@binu_acs Here are the actual unit numbers. I am trying not to make any manual changes to the actual spreadsheets our clients send us. Once I get these workflows built I will schedule them to run each month; if I have to make manual changes; then I wont be able to automate them without touching them first.

1-1901 Lashion Alexander
1-1902 Catrina Henderson
1-190 Quadell Denson
1-1904 David Piper
1-1905 Pablo Haynes
1-1906 Lawrence Marshall
1-1907 Stephanie French, Bryton Paul
1-1911 Nicole Ransom
1-1912 Jordan Williams
1-1913 Kentella Wilson

sslattery17

@Qiu Yes I am pulling in spreadsheets; but prefer not to modify the spreadsheet from the client so I don't have to touch it every month once I buildout the workflow and schedule it to run. They send monthly reports....

Qiu

@sslattery17
thank you for the udpate.
Is it possible that you can share share the Excel file containing the data above?

Qiu

@sslattery17
The issue now is that Excel starts usually 1900 year while Alteryx Starts 1400.

Excel supports two date systems, the 1900 date system and the 1904 date system. Each date system uses a unique starting date from which all other workbook dates are calculated. All newer versions of Excel calculate dates based on the 1900 date system, while older versions used the 1904 system.

binu_acs

CoG

you can try and setup that field as a formula in excel, by using the Formula Tool in Alteryx with the following update to Unit #:
'="' + [Unit #] + '"'

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703