cancel
Showing results for 
Search instead for 
Did you mean: 

convert number to date

former_member229754
Participant
0 Kudos

Dear All,

I've got this issue

I have for example a date in number format: 42005 = 01/01/2015

i have in database by number, How can I convert it into date format in webi?

thanks

REgards,

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor

try this.Number is the object name which hold the numeric values.

=RelativeDate(ToDate("01/01/1900";"dd/MM/yyyy");[Number]-2)

Loed
Active Contributor
0 Kudos

Hi Amit,

What's the purpose of [Number]?

Regards,

Loed

amitrathi239
Active Contributor
0 Kudos

Number is the dummy object name which i have considered for numeric values.e.g 42005.

Let say you have object dummy which hold the date numeric values.Then you have to use dummy in the formula.

dates numeric values are started from date 01/01/1900.

like this.

01/01/1900 -1

02/01/1900 -2

In the formula have  added the numeric values in the date 01/01/1900 to get the actual date.

if you place =DATEVALUE("01/01/1900") in the excel cell you will get 1.

Loed
Active Contributor
0 Kudos

Hi,

Sorry I did not see the NUMBER column..Haha..

Thank you..

Loed

Answers (1)

Answers (1)

Loed
Active Contributor
0 Kudos

Hi,

What is your source? How did you arrive with value of 42005? Did you already try to use the FORMATDATE function?

Regards,

Loed

former_member229754
Participant
0 Kudos

That value is like coming from Excel.

try to do this

put 42005

and put 'Short Date'

and you will see.

FormatDate will convert the typical date, but, this is not in date format)

Loed
Active Contributor
0 Kudos

Hi,

So it is a data problem..Why don't you correct your EXCEL file first? Just right click the column of your date then format it with YYYYMMDD or any format you like..

REgards,

oed

former_member229754
Participant

Hi Loed,

that is not possible.

Let me explain you the scenario:

this value is coming up from BPC Cube, the planning Date is inserted in number format in kf.

so, I need to convert it back in WeBI.

got it?