My macro sets old dates to 2002-more date issues
Posted by Vanessa on January 09, 2002 10:25 AM
I use a macro to import a .Lis text -delimited format file into Excel as part of my procedure in which the dates appear as Jan-01 Feb-01 etc, when importing them this month it converted them to 1/1/02 2/1/02 etc. this runs into major errors as I create charts and reports. Help?!
thanks a million!
Posted by Scott on January 09, 2002 11:45 AM
Reformat: Custom mmm-dd (NT)
Posted by Vanessa on January 09, 2002 11:58 AM
Re: Reformat: Custom mmm-dd (NT)-not working
I had a mistake in my post, the text(lis) file says 01-Jan and I need it to read it as January 2001
instead of Jan 1st 2001 by default when it converts it into excel.
When January data for 2002 comes in it will read
02-Jan and understand Jan 2nd 2002..
that custom format still converts it to 2002
thanks!
Posted by Mark W. on January 09, 2002 12:26 PM
Vanessa, Excel interpets "Jan-01" as January 1st --
not January 2001. Excel also assumes that in the
the absence of a "yy" or "yyyy" specification that
you mean "the current year". Change your macro to
read "Jan-01" and translate to "Jan-2001".
Posted by Mark W. on January 09, 2002 12:30 PM
As we learned in 1999's Y2K crisis it's best to use a 4-digit representation for year data (nt)
Posted by Scott on January 09, 2002 12:32 PM
Re: Reformat: Custom mmm-dd (NT)-not working
Here is a formula you can use to convert something like 01/02/01 into Jan-02:
=VALUE(MONTH(A1)&-(DAY(A1)+2000))
You have to format it to Month and Year (Jan-02)
It will work on 2001 and forward years.
Posted by Vanessa on January 09, 2002 2:27 PM
Re: As we learned in 1999's Y2K crisis it's best to use a 4-digit representation for year data (nt)
Mark How do I get excel to do this actually it is in the lis (text) file as
01-Jan (I read it wrong earlier)
how do I run the macro to update all of the dates
written in the same manner to read Year-Month ?
Posted by Mark W. on January 09, 2002 3:21 PM
Re: As we learned in 1999's Y2K crisis it's best to use a 4-digit representation for year data (nt)
I'd open the text file using Excel's File | Open...
menu command which will invoke the Text to Column...
wizard. At Step 3 of 3 I'd apply a Text format
to your date values. After pressing [ Finish ]
I'd insert a new worksheet column to the right
of the column containing the text representation
of your dates and enter a formula to translate
the value to the proper date. For example, let's
assume that column A contains the text representation
of your date value. The formula...
=DATEVALUE(RIGHT(A1,3)&"-20"&LEFT(A1,2))
...will assign a date in this millenium. Of
course as I said previously, you really ought
to have the producer of your text file change
their ways and use "yyyy" instead of "yy".
Posted by Aron on January 09, 2002 7:55 PM
Try this...
Excel will automatically turn a yy year to a yyyy year if you reneter the date. You can do this very quickly with a macro such as the following:
Sub changeDate()
For each c in Worksheets("Sheet1").Range_("Range").Cells
c.activate
let f = c.value
c.value = f
Next
End Sub
Replace "Sheet1" above with the sheet name, and "Range" above with the range containing the dates that need changing, i.e. "A1:A100". All this does is reinsert what is there, and Excel automaticallly changes it to yyyy. Then you can 'Format->Cells' to make it look any way you'd like.
Good luck!
Aron
