cancel
Showing results for
Did you mean:

Alteryx designer Discussions

SOLVED

Excel 1997-2003 Format Input - Need To Skip Rows

Meteoroid

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.

Magnetar

Hi again @barrnone

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.

Cheers,

Esther

Highlighted
Meteoroid

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.

 CURRENCY2 Field_13_3 Current Format number SPOT RATE2 % Diff LBP 1899-12-30 00:01:15 1899-12-31 00:01:15 0.0008680556 0.0008712636 0.37% LKR 1899-12-30 00:10:36 1899-12-31 00:10:36 0.0073611111 0.0073647173 0.05% LVL 1900-01-02 14:02:31 1900-01-02 14:02:31 2.5850810185 2.5850773663 0.00% MAD 1899-12-30 03:18:29 1899-12-31 03:18:29 0.1378356481 0.1378301150 0.00% MXN 1899-12-30 01:38:53 1899-12-31 01:38:53 0.0686689815 0.0686632746 -0.01% MYR 1899-12-30 07:42:50 1899-12-31 07:42:50 0.3214120370 0.3214143469 0.00% NDC 1899-12-30 04:42:41 1899-12-31 04:42:41 0.1963078704 0.1963031765 0.00% NOK 1899-12-30 03:44:17 1899-12-31 03:44:17 0.1557523148 0.1557469353 0.00% NZD 1899-12-30 21:46:41 1899-12-31 21:46:41 0.9074189815 0.9074212569 0.00% PEN 1899-12-30 09:28:40 1899-12-31 09:28:40 0.3949074074 0.3949100004 0.00% PGK 1899-12-30 09:21:55 1899-12-31 09:21:55 0.3902199074 0.3902180747 0.00% PHP 1899-12-30 00:36:19 1899-12-31 00:36:19 0.0252199074 0.0252149300 -0.02% PKR 1899-12-30 00:13:35 1899-12-31 00:13:35 0.0094328704 0.0094319136 -0.01% PLN 1899-12-30 08:27:47 1899-12-31 08:27:47 0.3526273148 0.3526256583 0.00% PYG 1899-12-30 00:00:19 1899-12-31 00:00:19 0.0002199074 0.0002174554 -1.13% ROL 1900-01-01 07:31:58 1900-01-01 07:31:58 1.3138657407 1.3138655714 0.00% RON 1899-12-30 07:37:11 1899-12-31 07:37:11 0.3174884259 0.3174853677 0.00% RUB 1899-12-30 00:28:53 1899-12-31 00:28:53 0.0200578704 0.0200614539 0.02% SAR 1899-12-30 08:24:27 1899-12-31 08:24:27 0.3503125000 0.3503127732 0.00% SDR 1900-01-01 20:06:29 1900-01-01 20:06:29 1.8378356481 1.8378312197 0.00% SEK 1899-12-30 03:29:04 1899-12-31 03:29:04 0.1451851852 0.1451849332 0.00% SGD 1899-12-30 23:25:25 1899-12-31 23:25:25 0.9759837963 0.9759809623 0.00% SKK 1899-12-30 01:27:42 1899-12-31 01:27:42 0.0609027778 0.0609032388 0.00% THB 1899-12-30 01:00:36 1899-12-31 01:00:36 0.0420833333 0.0420853796 0.00% TRY 1899-12-30 06:05:03 1899-12-31 06:05:03 0.2535069444 0.2535114798 0.00% TTD 1899-12-30 04:38:39 1899-12-31 04:38:39 0.1935069444 0.1935043589 0.00% TWD 1899-12-30 01:01:37 1899-12-31 01:01:37 0.0427893519 0.0427902559 0.00% TZS 1899-12-30 00:00:49 1899-12-31 00:00:49 0.0005671296 0.0005670546 -0.01% UAH 1899-12-30 01:08:28 1899-12-31 01:08:28 0.0475462963 0.0475439644 0.00% USD 1900-01-01 07:32:05 1900-01-01 07:32:05 1.3139467593 1.3139500000 0.00% UYU 1899-12-30 00:58:04 1899-12-31 00:58:04 0.0403240741 0.0403211776 -0.01% VEB 1899-12-30 00:00:53 1899-12-31 00:00:53 0.0006134259 0.0006118687 -0.25% VEF 1899-12-30 00:00:00 1899-12-31 00:00:00 0.0000000000 0.0000052867 100.00% VND 1899-12-30 00:00:05 1899-12-31 00:00:05 0.0000578704 0.0000566258 -2.20% XCD 1899-12-30 11:38:09 1899-12-31 11:38:09 0.4848263889 0.4848212441 0.00% ZAR 1899-12-30 02:22:34 1899-12-31 02:22:34 0.0990046296 0.0990102164 0.01%
Labels