Get Data Excwl table causes system date changes

[ashley95e]

Get Data Excel table causes system date changes - nothing works , even input in Access first causes more problems in BI.

[ashley95e · Accepted solution]

Never mind .

=DATEVALUE(IF(LEN(A7809)=8,(LEFT(A7809,2)&"-"&IF(MID(A7809,3,3)="jly","jul",MID(A7809,3,3))&"-20"&MID(A7809,6,2)),(LEFT(A7809,1)&"-"&IF(MID(A7809,2,3)="jly","jul",MID(A7809,2,3))&"-20"&MID(A7809,5,2))))

asHley85e

[ashley95e]

BI and excel are not compatible at all. Any formatting in Excel goes out the window when importing into BI. For instance in prepping my data for BI to avoid problems I elected to clean my data in Excel to minimize having to reformat data in BI (I should mention that the data was not fully created in excel, but a combination of data from different sources)–I used - Trunc(rounded( ))-  in Excel to keep Float data clean at 2 decimal places, and again minimize BI’s formatting . To keep the dates from converting to system 1904  I  used “ =TEXT(S2,"mm/d/yyyy" )” -with all the careful attention to my data to avoid breaking the database, it broke any way, killing all my reports! Now I have to start over from scratch, once the controls break there is no way to recover.    Yeah, I should’ve saved, but I didn’t believe I was doing anything overly complicated.

asHley96e -

[ashley95e · Accepted solution]

Never mind .

=DATEVALUE(IF(LEN(A7809)=8,(LEFT(A7809,2)&"-"&IF(MID(A7809,3,3)="jly","jul",MID(A7809,3,3))&"-20"&MID(A7809,6,2)),(LEFT(A7809,1)&"-"&IF(MID(A7809,2,3)="jly","jul",MID(A7809,2,3))&"-20"&MID(A7809,5,2))))

asHley85e

[ashley95e · Accepted solution]

After converting to a date structure using Datevalue and are still having issues, add a new column and use = to refer to the Datevalue column. I also Data type as Long Date (I did this to insure no confusion with 2 digit years- '2014 for '14- if there was a problem there it's avoided). After that I converted the table to CSV file to enforce all the assigned data types- When dealing with huge databases I really dont want to revisit this issue!!

Example

Column AA has the date value formula                  Column AB references AA

(Datevalue formula)                                                  =AA

asHley95e

[ashley95e]

If you're still having issues save the table as CSV file, so far it appears it forces the column data types you assigned to each column