Hi
I have a date column in Excel that contains dates in the numerical format (for example 42349 would be 12/11/2015). I have tried parsing or changing the format of the column but Alteryx does not support this kind of a date and generates a null value when operated upon.
I am trying to automate the process of preparing a model so changing th format of the date in Excel is not an option. Also, the data exceeds 1 million rows so Excel won't support it either. How can change this to a date format?
Thanks,
Arjun
Solved! Go to Solution.
This proved very helpful today. Thank you!
I am attempting to do the opposite. My date is in mm-dd-yyyy format, but I need the Excel 5 digit, post 1900 number. IS there a way to convert the date?
RodL--Thank you very much for your solution. After trying it out, I realized your logic was mostly sound but wasn't quite fool-proof since I still got times that were off by 1 sec occasionally. I used your thought process to sketch out a full, fool-proof solution that got me the right answer 100% of the time for the full date & time.
Step 1 - Create the base set of data to work with (all "double" data types):
Step 2 - Resolve the correct date & time parts one at a time using only the base data set (doing this eliminates having to perform the steps in any particular order):
Step 3 - Resolve the date portion of the date & time:
Step 4 - Resolve the full date and time using [DATE_RESOLVED] created in Step 3:
Thank you Jordan!
We can simply do:
1) First change the 'Dummy_Date' field data type with 'double'
2) then in formula tool use ToDateTime([Dummy_Date]) and change data type with 'DateTime'.
Done
I know this is an old ticket but I was looking for this solution and this is the ticket that got me started.
I have come up with something a bit slimmer, I have tested it and it works as far as I can see. I have added the example workflow.
If the Date Serial is 43831.588530092595 first thing is to separate the whole number and the decimal.
Date:
Whole Number = 43831 = Number of days since 1900-01-01 (you need to take 2 days off, due to excel leap year error and to account for the first day)
DateTimeAdd("1900-01-01",43831-2,"days") = 2020-01-01
Time:
Decimal = 0.588530092595 = fraction of seconds since 00:00:00. Find the number of seconds since midnight by 1/86400 which is equivalent to a 1 second as a fraction. 0.588530092595 / (1/86400) is 50849.
DateTimeAdd("2020-01-01",50849,"seconds") = 2020-01-01 14:07:29
* I do some rounding as I found if the decimal was to long it would drop a second.
Sorry if this isn't the best explanation, hope the actual workflow helps. 😀
Seriously life saver!