How to convert the property member in DAte format for ex: 20041028 to 2004/10/28?

[pankajkumar_gilda79]

Hi All,

    I have a Dimension where all the Asset Items are maintained. For each Asset Item there is Date of Acquisition which I have maintained it as a property "DOA" in the Dimension.

Now when I am loading the Master data from BW info provider the date of acquisition is maintained  in 2004.10.28 format.  Load is successful but the in BPC it is populating as 20041028 in the DOA column of the Dimension.

Next I am bringing the DOA in Excel Input form to show the user when the asset was capitalized.  In the same form I need to use the Excel "DAYS" functionality to decide on the number of days it has been used. When entered the formula :

Asset Item	DOA	DOR	NOD
ASSET1	20041028	2016/03/25	=DAYS(ENDDATE,STARTDATE) ; result ###########

How to convert the DOA in proper date format. Any idea please share it.

Regards

KMG

[former_member186338 · Accepted solution]

In Excel dates are integers of days since 1900.01.01

In excel you can also convert C3 containing 20041028 to date using:

=DATE(LEFT(C3;4);MID(C3;5;2);MID(C3;7;2))

[former_member186338 · Accepted solution]

P.S. You can also use JavaScript in the conversion file to get an integer from the string that will show correct date in Excel:

js:25570+(+new Date(%external%.substr(0,4),%external%.substr(4,2)-1,%external%.substr(6,2))- +new Date(1970,0,2))/86400000

assuming that %external% is 20041028

result will be 38288 - in Excel date will be 2004.10.28

Vadim

[pankajkumar_gilda79 · Accepted solution]

Thanks Vadim

Regards

KMG