Problems with dates!

  • Thread starter Thread starter Blakeaa
  • Start date Start date
B

Blakeaa

Hello everyone. I'm hoping someone might be able to help me out with a
problem.

I'm pasting some text into an excel worksheet. One column in the
source text is dates in the format dd-mm (example: 01-09 = Sept. 1st).
When I paste this into excel, or just type it for that matter, excel
converts it to "1/9/2003" which it then interprets as Jan 9th! How do
I prevent this behavior? I need to input the text as 01-09, but in
need excel to interpret it as Sept. 1st.

Thanks,
Blake
 
With A1 and downward being your existing imported data, such as 1/9/2003
Enter into B1 =TEXT(A1,"mm-dd") and copy downward ' then copy results from
B:B to B:B using Paste-Special - Values; Then delete ColA.

Back up before trying..............

HTH
 
The problem with this is once the data is imported it's no longer
correct. The date will be treated like Jan. 9th instead of Sept. 1st.
 
Don't paste it, import it as text instead, then use a help column and a
formula,
or change the regional date settings temporarily in windows (control panel).
Another way would be to correct it through a formula, I assume a date like
13-09 will come in as text, then you can just paste them and in a help
column use something like

=IF(ISNUMBER(A1),DATE(2003,DAY(A1),MONTH(A1)),DATE(2003,RIGHT(A1,2),LEFT(A1,
2)))

copy down as long as needed, format as dd-mmm
then paste special as values in place and finally delete the original column
This is probably on of the more annoying things with Excel when it tries to
interpreting things like this..
 
Are you saying that it imports it as text but the dates are changed,
I find that hard to believe?
Can you give an example how the text looks like for these 2 dates

09/01

09/13

using mm/dd
 
No, if it is "imported" as Jan 9th << this is a formatting (display) issue;
what it is REALLY Being imported as is a NUMBER; Prove that by clicking
on the cell and doing a Format, cell, Number Tab - General ,,,there you will
see that actual value that is in the cell >>> 37630 !! OK. It is
only displaying (by default) as 1/09/2003..

So, my =TEXT(A1,"mm-dd") formula will still work..

Anyway, go with Peo's recommendation - he knows a lot more than I do..
 
Back
Top