This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
I don't exactly know what you mean by date calender but this is what I know (copied from the web):
Excel (by default) uses the 1900 date system. This simply means that the date 1 Jan 1900 has a true numeric value of 1, 2 Jan 1900 has a value of 2 etc. These values are called "serial values" in Excel and it is these serial values that allows us to use dates in calculations.
If you put in any number > 0 in Excel and change the format to "Short Date" or any of the other date formats, it wil convert it to a date using the definition above. Hope this is what you were asking for. Thanks!
There is a slight amendment needed to that date conversion module you posted. You need to minus 2 off the number provided by Excel, due to the issue with 29th Feb 1900 and the inclusiveness of the start/end dates. (1900-01-01 is day 1 not day 0, and 1900 was not a leap year but excel calculates it wrongly).
One thing you can do is split the data into two components...the day (which is the whole number) and the time (which is the decimal part).
Once you have that, you can convert the day using the DATETIMEADD function. You can then convert the decimal part into a time and then concatenate the two results back for your full date time.
I've attached a workflow that shows an example of how to do the latter. There's probably more elegant ways to do this (for which I will let the "regular responders" chime in :smileywink:) but this works.