converting numbers into a date

[VILLABILLA]

Hello,

I am extracting data from an administrative system into Excel, dates
recieve in the following format;

For example:

May the 13th 2004 comes in as:

40513

So that is 4(2004),05(May),13(the 13th).

If I go to format cell, number and try to set it to date format i
comes up with wrong dates.

My question: How can change my list with numbers into dates?

Whether 40513 comes out as 13 May 2004 or as 04-05-13 or whatever, i
really doesn't matter... as long as Excel recognizes them as dates s
that I can use them in my Pivot table and chart...

Please help

 

[Andy B]

Hi

Try this:
=DATE(LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2))
This only works for 5 digit values, though - and that's assuming that the
year is one digit. For a six digit number use:
=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
Data / Text to Columns is also an option, but only for six digit values.

 

[VILLABILLA]

Hi Andy thanks a lot for your help, unfortunatelly I can't get it t
work:

30116 Meaning 16th of january 2003 comes out as 16/01/1903 (5 digi
formula) and 16/01/1930 with the 6 digit formula.

any idea what I'm doing wrong or how I can solve this

 

[VILLABILLA]

Looking at it again it seems that the DATE formula converts the 3 int
1903 that should be 2003, what can I do about that

 

[Andy B]

Hi

Try this:
=DATE("200"&LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2))
This will force it into 2000 and something (depending on the first digit)!

 

[VILLABILLA]

Yeah, that works great. Thanks a lot Andy

 

[VILLABILLA]

I would like to create a list that only consists of the months. If
change the cell format in such a way that it only shows the month, i
is still treated in my pivot table as a whole date including day, mont
and year.

Please let me know how I can do this

 

[VILLABILLA]

I just found out that this is possible with the TEXT function.

So I worked it out like that...

Regards