Date Tranformation

[wenyuanalive]

Hey guys,

I have a question about date transfer.

In an excel spreadsheet, cells(1,1) = 28Feb90. How can I manipulate this data to get cells(1,2) = 199002(meaning Feb, 1990)? It looks like Excel can't read the short 'Feb' as February.

The data ranges from 31Jan90 t0 40Apr12. So I was wondering if there is a quick way to transfer. Or should I write a user-defined function?

Thank you and enjoy the rest of the weekend.

Sean

[GTO]

When you select A1, what shows in the formula bar? Also, if you widen the column a bit (presuming alignment is left at General), which side does the "date" fall to, left or right?

[wenyuanalive]

When you select A1, what shows in the formula bar? Also, if you widen the column a bit (presuming alignment is left at General), which side does the "date" fall to, left or right?


In A1 there is a user-defined formula developed by S&P 500. The "date" falls to left.

I sort of figured out that the formula in B1 is something like:
=IF(MONTH(A1)<10,YEAR(A1)&"0"&MONTH(A1),YEAR(A1)&MONTH(A1))

[GTO]

Is it perhaps some type of DDE or query that automatically downloads data from the web? I am not very familiar with that type of operation, but thus far, I am unable to replicate the issue. If I enter 05May90 for instance, Excel catches it and coerces the string to a legitimate date. If it going off left, then Excel is recognizing it as text. Check the cell's numberformat; what is that?

[shrivallabha]

Maybe OP has manually left-aligned dates while they are actual dates. As he has used Date-related functions in his next formula.

[Bob Phillips]

In A1 there is a user-defined formula developed by S&P 500. The "date" falls to left.

I sort of figured out that the formula in B1 is something like:
=IF(MONTH(A1)<10,YEAR(A1)&"0"&MONTH(A1),YEAR(A1)&MONTH(A1))

=TEXT(A1,"yyyymm") is simpler

[wenyuanalive]

=TEXT(A1,"yyyymm") is simpler

That's awesome!!