Import Data Date Format Wrong

[Renier]

Good day,
Below is an example of how the date is displayed in a report I download. But Excel does not recognize this as a date. My regional formatting for dates is 2023-12-04 and I don't really want to change the regional formatting if possible.

Is there a way to get the imported data into the correct format?

Thanks,

Start Time	End Time
14/12/2023 4:08:16 PM	14/12/2023 4:08:43 PM
14/12/2023 4:06:42 PM	14/12/2023 4:07:48 PM
14/12/2023 4:05:30 PM	14/12/2023 4:05:48 PM
14/12/2023 4:04:40 PM	14/12/2023 4:05:15 PM
14/12/2023 4:04:02 PM	14/12/2023 4:06:29 PM
14/12/2023 3:54:29 PM	14/12/2023 3:55:03 PM
14/12/2023 3:53:55 PM	14/12/2023 3:55:35 PM
14/12/2023 3:47:43 PM	14/12/2023 3:48:48 PM
14/12/2023 3:44:40 PM	14/12/2023 3:44:43 PM
14/12/2023 3:42:06 PM	14/12/2023 3:44:44 PM
14/12/2023 3:41:48 PM	14/12/2023 3:43:05 PM
14/12/2023 3:38:49 PM	14/12/2023 3:39:18 PM
14/12/2023 3:38:37 PM	14/12/2023 3:43:01 PM
14/12/2023 3:37:28 PM	14/12/2023 3:38:37 PM
14/12/2023 3:37:19 PM	14/12/2023 3:37:38 PM
14/12/2023 3:37:00 PM	14/12/2023 3:38:04 PM
14/12/2023 3:36:46 PM	14/12/2023 3:40:37 PM
14/12/2023 3:33:50 PM	14/12/2023 3:39:01 PM

 

[Georgiboy]

Have you tried running text to columns on one of the columns?

 

[Renier]

Have you tried running text to columns on one of the columns?

Hi,
I have indeed. And I set the date format to DMY, but no luck.

 

[Georgiboy]

I see, I can't replicate the error my end as that is a UK date and my UK settings recognise it as a date.

What happens if you double click into a cell and then press enter, does it stay as text?
I assume the column is not formatted as text (sorry, I had to ask)?

 

[Renier]

I see, I can't replicate the error my end as that is a UK date and my UK settings recognise it as a date.

What happens if you double click into a cell and then press enter, does it stay as text?
I assume the column is not formatted as text (sorry, I had to ask)?

Yip, stays as text and is formatted as a date.

Thank you for trying to help.

 

[Georgiboy]

What happens if you run something like the below:

Sub test2()
    Dim rng As Range, var As Variant, x As Long
    Dim tVar As Variant
   
    Set rng = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
   
    For x = 1 To UBound(var)
        tVar = Split(var(x, 1), " ")
        var(x, 1) = CDate(tVar(0)) & " " & tVar(1) & " " & tVar(2)
        tVar = Split(var(x, 2), " ")
        var(x, 2) = CDate(tVar(0)) & " " & tVar(1) & " " & tVar(2)
    Next x
    rng = var
End Sub

 

[Renier]

What happens if you run something like the below:

Sub test2()
    Dim rng As Range, var As Variant, x As Long
    Dim tVar As Variant
 
    Set rng = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    var = rng.Value
 
    For x = 1 To UBound(var)
        tVar = Split(var(x, 1), " ")
        var(x, 1) = CDate(tVar(0)) & " " & tVar(1) & " " & tVar(2)
        tVar = Split(var(x, 2), " ")
        var(x, 2) = CDate(tVar(0)) & " " & tVar(1) & " " & tVar(2)
    Next x
    rng = var
End Sub

I'm sorry to waste your time but I have no idea how to do that!

 

[Georgiboy]

If you are not using the online version of excel then:

press ALT+F11
In the new window that opens known as the VBE:
Go to 'Insert' in the ribbon
Select 'Module'
Paste the code I posted into the large white space

Go back to your main Excel window
Press ALT+F8

Select the macro & press run

 

[Renier]

If you are not using the online version of excel then:

press ALT+F11
In the new window that opens known as the VBE:
Go to 'Insert' in the ribbon
Select 'Module'
Paste the code I posted into the large white space

Go back to your main Excel window
Press ALT+F8

Select the macro & press run

Thanks!

 

[Georgiboy]

You're welcome, thanks for the feedback. Worth noting that if you want to keep the code in the spreadsheet for future use then you will need to save the file as a 'Macro enabled workbook' this type of file has the '.xlsm' extension rather than the 'xlsx'