Converting Text to Date - text to columns function

[ben_sa]

Good morning,

I got a new data set but the dates appear as text a not date - March 25, 2022 (it comes as Month, Day, Year format).

I was trying to convert them to date by using the text to columns function but it only managed to convert the earliest data from Mar to May to dates but the latest rows ( Jun & Jul) were kept in text format.

Does anybody have any idea why it isn't converting the entire column to dates or is there a better way to execute this?

Thank you,

 

[Joe4]

Welcome to the Board!

I expect maybe there are extra non-visible characters (like non-breaking spaces in those cells).

One way to check is to take one of the cells that is not converting, and apply the LEN function on it.

For example, if "July 1, 2022" was in cell A10, place this formula in some unblank cell and see what it returns:
=LEN(A1)
One would expect it to return 12. If it returns anything more than that, you have some extra characters in there messing things up.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Converting Text to Date - text to columns function
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

I was trying to convert them to date by using the text to columns function

What did you select as the date format?

 

[ben_sa]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Converting Text to Date - text to columns function
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thank you for letting me know, I wasn't aware of that.

 

[Fluff]

Can you answer my question in post#4?

 

[Alex Blakenburg]

I have no idea why the Text to Columns date conversion is having an issue with the 4 character months.
Your profile does not show what version of Excel you are running, you might want to update that.
Power Query doesn't seem to have an issue with the conversion if you want to try that.

Alternatively you could try the macro below:

Sub SelectedCellsDateTypeConvert()
    Dim arr As Variant
    Dim i As Long, j As Long
    
    If Selection.Areas.Count > 1 Then
        MsgBox "Please select a contiguous range"
        Exit Sub
    End If
    
    ' Use intersect to cater for whole column selection
    arr = Intersect(Selection, ActiveSheet.UsedRange)

    On Error Resume Next
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr)
            If arr(i, j) <> "" Then
                arr(i, j) = CDate(arr(i, j))
            End If
        Next j
    Next i
    On Error GoTo 0
    Intersect(Selection, ActiveSheet.UsedRange) = arr
    
End Sub

 

[ben_sa]

Can you answer my question in post#4?

Good morning, sorry for my late response. Apparently, it was a mistake in the setting configuration on the back end side so I was able to receive the file with the correct format date.

 

[Fluff]

Glad you sorted it & thanks for letting us know.