Date Conversion

[MAXINE TOWNS]

NEED TO CONVERT A DATE WHICH CAN BE EITHER IN A 7 CHARACTER FORMA
(1031128 FOR 28NOV03) OR 6 CHARACTER FORMAT (981128 FOR 28NOV98)

THINK IT NEEDS TO INCLUDE: IF, LEN AND DATEVALUE COMMANDS

E.G. IF 7 CHARACTER LENGTH THEN... OTHERWISE

AIM: CONVERT TO A 28-NOV-03 FORMAT

ANY IDEAS WELCOME!

THANKS

 

[Paul]

NEED TO CONVERT A DATE WHICH CAN BE EITHER IN A 7 CHARACTER FORMAT
(1031128 FOR 28NOV03) OR 6 CHARACTER FORMAT (981128 FOR 28NOV98)

THINK IT NEEDS TO INCLUDE: IF, LEN AND DATEVALUE COMMANDS

E.G. IF 7 CHARACTER LENGTH THEN... OTHERWISE

AIM: CONVERT TO A 28-NOV-03 FORMAT

ANY IDEAS WELCOME!

THANKS.

Perhaps you could turn off your Caps Lock!

One possible formula:
=IF(LEN(A1)=7,DATE(LEFT(A2,3),MID(A2,4,2),RIGHT(A2,2)),DATE(LEFT(A3,2),MID(A
3,3,2),RIGHT(A3,2)))

 

[Paul]

Perhaps you could turn off your Caps Lock!

One possible formula:
=IF(LEN(A1)=7,DATE(LEFT(A2,3),MID(A2,4,2),RIGHT(A2,2)),DATE(LEFT(A3,2),MID(A
3,3,2),RIGHT(A3,2)))

Sorry, that should have been:
=IF(LEN(A1)=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),DATE(LEFT(A1,2),MID(A
1,3,2),RIGHT(A1,2)))

 

[Don Guillett]

Please do NOT type in all caps. It is considered shouting and rude.
try this ONE line fomula

=IF(LEN(C1)=7,DATE(MID(C1,2,2),MID(C1,4,2),RIGHT(C1,2)),DATE(MID(C1,1,2),MID
(C1,3,2),RIGHT(C1,2)))

You may use this to make one line. Leave a line above
Sub FixLongFormulas() 'select 1st line Don Guillett
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
'Cells(x - 1, y) = Cells(x - 1, y) & C
mstr = mstr & C
Next
Cells(x - 1, y) = mstr
End Sub