community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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.

 

Thanks for your help!

Pulsar

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.

CURRENCY2Field_13_3Current FormatnumberSPOT RATE2% Diff
LBP1899-12-30 00:01:151899-12-31 00:01:150.00086805560.00087126360.37%
LKR1899-12-30 00:10:361899-12-31 00:10:360.00736111110.00736471730.05%
LVL1900-01-02 14:02:311900-01-02 14:02:312.58508101852.58507736630.00%
MAD1899-12-30 03:18:291899-12-31 03:18:290.13783564810.13783011500.00%
MXN1899-12-30 01:38:531899-12-31 01:38:530.06866898150.0686632746-0.01%
MYR1899-12-30 07:42:501899-12-31 07:42:500.32141203700.32141434690.00%
NDC1899-12-30 04:42:411899-12-31 04:42:410.19630787040.19630317650.00%
NOK1899-12-30 03:44:171899-12-31 03:44:170.15575231480.15574693530.00%
NZD1899-12-30 21:46:411899-12-31 21:46:410.90741898150.90742125690.00%
PEN1899-12-30 09:28:401899-12-31 09:28:400.39490740740.39491000040.00%
PGK1899-12-30 09:21:551899-12-31 09:21:550.39021990740.39021807470.00%
PHP1899-12-30 00:36:191899-12-31 00:36:190.02521990740.0252149300-0.02%
PKR1899-12-30 00:13:351899-12-31 00:13:350.00943287040.0094319136-0.01%
PLN1899-12-30 08:27:471899-12-31 08:27:470.35262731480.35262565830.00%
PYG1899-12-30 00:00:191899-12-31 00:00:190.00021990740.0002174554-1.13%
ROL1900-01-01 07:31:581900-01-01 07:31:581.31386574071.31386557140.00%
RON1899-12-30 07:37:111899-12-31 07:37:110.31748842590.31748536770.00%
RUB1899-12-30 00:28:531899-12-31 00:28:530.02005787040.02006145390.02%
SAR1899-12-30 08:24:271899-12-31 08:24:270.35031250000.35031277320.00%
SDR1900-01-01 20:06:291900-01-01 20:06:291.83783564811.83783121970.00%
SEK1899-12-30 03:29:041899-12-31 03:29:040.14518518520.14518493320.00%
SGD1899-12-30 23:25:251899-12-31 23:25:250.97598379630.97598096230.00%
SKK1899-12-30 01:27:421899-12-31 01:27:420.06090277780.06090323880.00%
THB1899-12-30 01:00:361899-12-31 01:00:360.04208333330.04208537960.00%
TRY1899-12-30 06:05:031899-12-31 06:05:030.25350694440.25351147980.00%
TTD1899-12-30 04:38:391899-12-31 04:38:390.19350694440.19350435890.00%
TWD1899-12-30 01:01:371899-12-31 01:01:370.04278935190.04279025590.00%
TZS1899-12-30 00:00:491899-12-31 00:00:490.00056712960.0005670546-0.01%
UAH1899-12-30 01:08:281899-12-31 01:08:280.04754629630.04754396440.00%
USD1900-01-01 07:32:051900-01-01 07:32:051.31394675931.31395000000.00%
UYU1899-12-30 00:58:041899-12-31 00:58:040.04032407410.0403211776-0.01%
VEB1899-12-30 00:00:531899-12-31 00:00:530.00061342590.0006118687-0.25%
VEF1899-12-30 00:00:001899-12-31 00:00:000.00000000000.0000052867100.00%
VND1899-12-30 00:00:051899-12-31 00:00:050.00005787040.0000566258-2.20%
XCD1899-12-30 11:38:091899-12-31 11:38:090.48482638890.48482124410.00%
ZAR1899-12-30 02:22:341899-12-31 02:22:340.09900462960.09901021640.01%
Labels