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.
Exactly. Excel has its own way of interpreting dates as numeric values. That's how we end up with things like 43684 for today's date (8/7/2019). There is an algorithm for converting that to a real date in other applications, including Alteryx, using a base date. But a formula in Excel won't be the same as the formula in Alteryx.
It looks like some of the precision will be lost in the conversion, unfortunately.
So, in the method outlined in the post i referenced, leave the first formula tool as is, adding one day to anything from 1899.
in the second formula tool, try this instead:
DateTimeDiff([F5],'1899-12-31 00:00:00','seconds')/86400. This calculates the difference between the "date" and a benchmark date of 12/31/1899 in seconds, and divides by the number of seconds in a day.
Not a perfect solution at all, but the numbers are closer to the original. So, the best solution might very well be running a command that converts the file type for you.
Thanks, @EstherB47. I kind of figured there would be some precision loss and that might not be acceptable for the end user. The loss is generally close to zero, but there is one that is coming up 0 in the calculation when it should have a value (VEF) and a few that are off by 1-2%. You can see that below -- number is coming from your formula, SPOT RATE2 is the actual value generated using the BAT file method to generate an xlsx file.