Date imported from CSV don't appear correctly

[hamohd70]

Hi,

I'm importing date from CSV file including date into excel. The date field in the CSV file is configured as "mm-dd-yy hh:mm:ss".

The date cells in the excel sheet is configured as dd-mmm-yy hh:mm:ss.

My problems is that the date imported will show correctly if the day is below 13 but will always show in the wrong format when the day is 13 and above.

an example:

[B]05-May-18 20:30:10[/B]    -> show correctly because the "day" in the original date is less than 13. (original date in the CSV files is 05/05/18 20:30:10)

[B]05-Dec-18  11:57:35[/B]   ->shows correctly still because the "day" in the original date is less than 13. (original date in the CSV files is 12/05/18 11:57:35)

[B]5/31/18 3:08:43[/B]         -> here the date will [U]not [/U]show correctly and will remain in the same format of the original file

How can I solve this issue please?

 

[Rick Rothstein]

Hi,

I'm importing date from CSV file including date into excel. The date field in the CSV file is configured as "mm-dd-yy hh:mm:ss".

The date cells in the excel sheet is configured as dd-mmm-yy hh:mm:ss.

My problems is that the date imported will show correctly if the day is below 13 but will always show in the wrong format when the day is 13 and above.

an example:

[B]05-May-18 20:30:10[/B]    -> show correctly because the "day" in the original date is less than 13. (original date in the CSV files is 05/05/18 20:30:10)

[B]05-Dec-18  11:57:35[/B]   ->shows correctly still because the "day" in the original date is less than 13. (original date in the CSV files is 12/05/18 11:57:35)

[B]5/31/18 3:08:43[/B]         -> here the date will [U]not [/U]show correctly and will remain in the same format of the original file

How can I solve this issue please?

I think you may have a bigger problem than you think. When you say 05-Dec-18 is displaying correctly, I'll bet you it isn't. I think if you check back in the file (you should do this to be sure), you will find that date was supposed to be 12-May-18. It is not possible for some of the dates to be correct and others not... they were all created in the same system and they all should be interpreted in the same way. The fact that some are not interpreted correctly must mean they are all not being interpreted correctly. If I am right (and I would be willing to bet I am), I believe we can create a macro for you to straighten everything out, but you need to tell us where your dates are located on the worksheet first.

 

[JonRowland]

I second Rick's observation as have experience this in the past and what you'll find is the date is formatted into US style of MM-DD-YY. So what you think Excel is displaying is 05-Dec-18 (05-12-18) the actually display date is May-12-18. Thus you can't trust the date in your data.

What you don't say is how your are importing the CSV. If use VBA (macro) all your need to added to the opening is local:=true, which stops Excel changing the format style.

 

[hamohd70]

I think you may have a bigger problem than you think. When you say 05-Dec-18 is displaying correctly, I'll bet you it isn't. I think if you check back in the file (you should do this to be sure), you will find that date was supposed to be 12-May-18. It is not possible for some of the dates to be correct and others not... they were all created in the same system and they all should be interpreted in the same way. The fact that some are not interpreted correctly must mean they are all not being interpreted correctly. If I am right (and I would be willing to bet I am), I believe we can create a macro for you to straighten everything out, but you need to tell us where your dates are located on the worksheet first.

you are right @Rick Rothstein: The date is actually 12-May-18 but when I say correct because using date functions with that date such as day() or Month() don't result in a #Value error and the date can be reconstructed again.

Do you think the problem can be solved using a macro? If so, then the date is stored in Column "A" only.

@JonRowland: the data is imported using Excel's Get External Data Wizard and then the date format is specified there.

thank you for your help

 

[Rick Rothstein]

you are right @Rick Rothstein: The date is actually 12-May-18 but when I say correct because using date functions with that date such as day() or Month() don't result in a #Value error and the date can be reconstructed again.

Do you think the problem can be solved using a macro? If so, then the date is stored in Column "A" only.

Give the following macro a try. I would suggest you test it on a copy of your worksheet, not the actual worksheet (as it will physically change the cell values) to make sure it works correctly.

[table="width: 500"]
[tr]
	[td]Sub FixDates()
  Dim R As Long, CellVal As Variant
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    CellVal = Cells(R, "A").Value
    If Application.IsNumber(CellVal) Then
      Cells(R, "A").Value = DateSerial(Year(CellVal), Day(CellVal), Month(CellVal))
    Else
      CellVal = Split(CellVal, "/")
      Cells(R, "A").Value = DateSerial(CellVal(2), CellVal(1), CellVal(0))
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[hamohd70]

Give the following macro a try. I would suggest you test it on a copy of your worksheet, not the actual worksheet (as it will physically change the cell values) to make sure it works correctly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub FixDates()
  Dim R As Long, CellVal As Variant
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    CellVal = Cells(R, "A").Value
    If Application.IsNumber(CellVal) Then
      Cells(R, "A").Value = DateSerial(Year(CellVal), Day(CellVal), Month(CellVal))
    Else
      CellVal = Split(CellVal, "/")
      Cells(R, "A").Value = DateSerial(CellVal(2), CellVal(1), CellVal(0))
    End If
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

It gave me Run-time error '9' Subscript out of range error and on other cells it gives a Type mismatch on this line

Cells(R, "A").Value = DateSerial(CellVal(2), CellVal(1), CellVal(0))

 

[Peter_SSs]

It gave me Run-time error '9' Subscript out of range error and on other cells it gives a Type mismatch on this line

Cells(R, "A").Value = DateSerial(CellVal(2), CellVal(1), CellVal(0))

I don't think Rick has taken account of the fact that the cells contain time values as well as date values. I'm sure he will address that when he next visits the thread.

 

[hamohd70]

I don't think Rick has taken account of the fact that the cells contain time values as well as date values. I'm sure he will address that when he next visits the thread.

That was my mistake ! I did not mention it since I was planning to trim it out.

The code works fine when I removed the time values.

thank you

 

[Rick Rothstein]

I don't think Rick has taken account of the fact that the cells contain time values as well as date values. I'm sure he will address that when he next visits the thread.

You are correct... I forgot about the time part. Thanks for the reminder.

That was my mistake ! I did not mention it since I was planning to trim it out.

The code works fine when I removed the time values.

You should not have to do that manually, so I modified my code to remove the time part for you. I also made sure each date displayed in dd-mmm-yy format. The changes I made to do both of these are highlighted in red.

[table="width: 500"]
[tr]
	[td]Sub FixDates()
  Dim R As Long, CellVal As Variant
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    CellVal = [B][COLOR="#FF0000"]Split([/COLOR][/B]Cells(R, "A").Value[B][COLOR="#FF0000"])(0)[/COLOR][/B]
    If Application.IsNumber(CellVal) Then
      Cells(R, "A").Value = DateSerial(Year(CellVal), Day(CellVal), Month(CellVal))
    Else
      CellVal = Split(CellVal, "/")
      Cells(R, "A").Value = DateSerial(CellVal(2), CellVal(1), CellVal(0))
    End If
    [B][COLOR="#FF0000"]Cells(R, "A").NumberFormat = "dd-mmm-yy"[/COLOR][/B]
  Next
End Sub[/td]
[/tr]
[/table]