Forum Discussion
Get Data Excwl table causes system date changes
Get Data Excel table causes system date changes - nothing works , even input in Access first causes more problems in BI.
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
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
4 Replies
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 -
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
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