Excel improper date format 1

[logius]

Well, wouldn't you know it? As soon as my application is finally running, Excel throws a wrench in the gears.

What I have is a chart generation macro using userforms and such to set ranges of user-defined data to include in graphs. Problem is, the data I have coming in via a .CSV file (being generated by another program I have no control over) is being interpreted improperly in Excel. The line in question is a set of dates ranging from Jan-98 up to whenever. Now, the format of the dates is just like you see ("mmm-yy&quot, that is, until it gets to the current year's data. Suddenly, the .CSV data format changes from Jan-98 to 01-Jan . Well, sweet old Excel interprets this as 1/01/2001, which is fine. However, I also have data extending into 2002, and the format also ends up as 02-Jan. Excel, though, is reading this as 1/02/2001 when it should be 1/01/2002. I've tried reformatting the data as "yy-mmm" then formatting it to "mmm-yy", but it still ends up as 1/02/2002. Any suggestions? ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[logius]

oops, I meant the data ends up as 1/02/2001. Heh. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[AidanEnos]

How predictable is the input file? If it's DEAD predictable - like lines 1-300 are in JAN-02 format and 301-500 are 02-JAN format then you should be able to tell the macro at what line to change the import parameters.

Aside from that... you may be able to use a conditional statement in the import macro... but VB isn't really my strong point. You should ask this on the VB forum and someone can tell you what you need.

on import if *,*,##-??? then
elseif *,*,???-## then

Can't help you with the specifics, sorry (

AidanEnos

 

[logius]

The only thing I can predict about the input file is where the dates begin (row 3, col E), aside from that, the number of dates (length of the row) can be variable for each generated file. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[logius]

Thanks, Aidan, I'm going to implement your idea. What I ended up doing is searching for an instance where the date format changes. Something like this:

Dim CellMonth As String
Dim CellYear As String
Dim CellValue As String
Dim MidValue As String
Dim CellItem As Object
Dim x as Integer
Dim DtFormat As Variant

DtFormat = ActiveSheet.Range("E3").NumberFormat

For Each CellItem in ActiveSheet.Range("E3", _
                     Range("E3").End(xlToRight))

   If Not CellItem.NumberFormat = DtFormat Then

      CellValue = WorksheetFunction.Text(CellItem.Value, _
                  CellItem.NumberFormat)
      CellMonth = Right(CellValue, 3)
      x = CellValue.Length - (CellMonth.Length + 1)
      CellYear = Left(CellValue, x)

      If CellYear.Length > 1 Then
         MidValue = " 01, 20"
      Else
         MidValue = " 01, 200"
      End If

      CellItem = CellMonth & MidValue & CellYear
      CellItem.NumberFormat = DtFormat
   End If

I put this code together in about 7 or 8 minutes, so don't rely completely on it. The only pain about it, though, is that if a cell with the date "1/01/2034" is entered, formatted to "mmm-yy" then saved in a CSV file, when you reopen the file, Excel will read it as "1/01/1934" and output it as "Jan-34" *sigh*, and my code relies on the format being "yy-mmm". ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com