PDA

View Full Version : [SOLVED:] Number to date


Cass
09-23-2005, 10:32 AM
raw data is pasted like this 10905 and is number format. how convert it 01.09.05 date format :dunno
10905
Airborne
09-23-2005, 10:39 AM
Hi Cass,

Right click on the target cell, click Format Cells, click date. Is this what you mean?

Regards,

Rob.
Cass
09-23-2005, 10:44 AM
:) no, i dont mean that. This give me 08.11.29
I need sort later this date

If i have :
10905
20905
90905
110805

and sort ascending

but maiby apportion data: day, month and year
rosen380
09-23-2005, 01:13 PM
Assuming your '10905' type date field is in a1 the following formula would convert it to an excel date field [assumes that the year will always be between 2000 and 2099]


=DATE(20 & RIGHT(A1,2), LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2))
Cass
09-23-2005, 01:27 PM
Excellent :friends: but this is only a little drop in sea (a)

continue next thread :think:
Cass
09-25-2005, 02:27 AM
Excellent :friends: but this is only a little drop in sea (a)

continue next thread :think:

It work only when modify a little ;)

=DATE(20 & RIGHT(A1;2);MID(A1;LEN(A1)-3;2); LEFT(A1;LEN(A1)-4))

Day and Month was exchanged http://vbaexpress.com/forum/images/smilies/039.gif