Issue with format of dates

Re: Text To Column Issue

Is there any reason why those cells should be formatted differently?

Because if not, you could always just change their formatting through 'Format | Cells | Number ' on the toolbar, and pick from the 'date' list the format you want. That would probably solve your problem, and you could apply it to your entire range very easily by highlighting.

Or am I misunderstanding your question?

EDIT: I tried it on your data, and to get the rogue cells to change, I had to format them as general first, and then apply the custom format. No idea why that is, but it did work. *shrug*

Hope that helps,

Re: Text To Column Issue

Sorry, edited as you posted.

Is there anything else 'special' about those cells? I'll have a look through your code to see if I can trace the problem, but it's puzzling me at the minute. :confused:

Re: Text To Column Issue

It's something to do with the leading zero in the problem cells, I'm sure. So far, had no luck trying to get round it though.

I have a suspicion that formatting as text for text-to-column, then converting back, may work, though it may involve messing round with copying twice to a separate column. I'll try, and report back.

Re: Text To Column Issue

Hi Reg,

I tried out my idea and no success; very sorry. I'm going to be out of the office this afternoon, so your best bet is probably to see if anyone else can field this. If any of the OzMVPs have a go, you'll probably be sorted!

Otherwise, I'll have another look tomorrow and see if I can solve it (it's bugging me as well, now!).

Kind regards, sorry I couldn't help much.

Re: Trouble With Hiding #DIV/0!

Reg,

However much you try to get round me by promoting me to OzMVP, you shouldn't really add a new problem to an existing thread. You could bump the existing thread for this problem back to the top of the queue for more attention by adding another comment to it.

That said, I will have a look at your problem.

What I would like to know is how the data in columns A & N is populated into the worksheet. The reason that the values in N16 & N17 are in a different format is that the second part of the date (in this case the day) is less than 13 and Excel is managing to interpret it as a date; what I don't know is whether the date in those cells is displayed in dd/mm/yy or mm/dd/yy format. Is N16 4 August or 8 April?

If you are working in non-US format dates, you will find that the internal processes of VBA will often automatically convert dates into US format unless you specifically code round it, which is why the Text To Columns is working as a spreadsheet function, but not in VBA.

As to how to overcome the problem, I would first of all like to go back to columns A & N to try to get the data there in a consistent format, as currently it's a mixture of text and dates. If you could let me know how the data gets in there and whether it would be possible to change that process that would be a start. If that's not possible, I will look at ways to convert the data as it stands.

Regards,
Batman.

Re: Trouble With Hiding #DIV/0!

Reg,

Your source data is obviously just opening up in the different formats, so we'll need to look at a way of converting from those different formats. By the way, when I tried Text To Columns manually on the first entry shown as a date, it converted to a 4 August, not 8 April.

Can you let me know what you want to do with the data imported? Do you just want a single new date column, separate date and time columns, broken down into separate columns for day, month and year, or something else?

Regards,
Batman.

Re: Trouble With Hiding #DIV/0!

Hi Reg,

The code below seems to work for me. Let me know if you have any problems with it.

Sub ReFormatDates()


    Dim rngDates As Range, rngCell As Range
    
    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("DATES")
        Set rngDates = .Range("N3:N" & .Range("N65536").End(xlUp).Row)
    End With
    
    For Each rngCell In rngDates
        With rngCell.Offset(0, 12)
            .NumberFormat = "dd/mm/yyyy"
            .Value = ConvertFromString(rngCell.Value)
        End With
    Next
    Application.ScreenUpdating = True
    
End Sub




Private Function ConvertFromString(ByVal strCellValue As String) As Date


    Dim strMonth As String, strDay As String, strYear As String, intPos As Integer, intPos2 As Integer
    
    intPos = InStr(1, strCellValue, "/")
    strMonth = Left(strCellValue, intPos - 1)
    intPos2 = InStr(intPos + 1, strCellValue, "/")
    strDay = Mid(strCellValue, intPos + 1, (intPos2 - (intPos + 1)))
    strYear = Mid(strCellValue, intPos2 + 1, 4)
    ConvertFromString = CDate(strDay & "/" & strMonth & "/" & strYear)
    
End Function

Regards,
Batman.

Re: Text To Column Issue

Hi Reg,

because the different formatting it appears to be some values as string and other as number (the one with "." !!)

try this out (maybe it is not the most elegant but it seems to work):

Sub Loopy()
  With Sheets("DATES")
    For i = 3 To 500
      If .Cells(i, 14) = "" Then Exit For
      jj = CDate(.Cells(i, 14))
'      .Cells(i, 15) = Month(jj)
'      .Cells(i, 16) = Day(jj)
'      .Cells(i, 17) = Year(jj)
'      .Cells(i, 18) = Time(jj)
      .Cells(i, 29) = Month(jj)
      .Cells(i, 30) = Day(jj)
      .Cells(i, 31) = Year(jj)
      .Cells(i, 32) = Format(jj, "hh:mm")
    Next i
  End With
End Sub

HTH

filo65

P.S.
Hopefully I understood right what u want to do!!

Re: Issue with format of dates

How does the data get into man.xls?
It is already wrong there. If the data is imported into man.xls, xl can be stopped from trying to interpret dates. At the moment, when it sees a valid English date it seems to flip month and day!
If the file it is imported from is text, then Data|Import External Data|Import Data allows you to specify DMY or MDY

I notice that the file is called 'man', is that because the data comes from the Man stocks and shares data feed supplier? If so, it looks like you've got future prices there - now that would be interesting!
Pascal