I have a column with Date and time in the same field, how to I split or change so I only have the date?

[ChrisCpH]

HI all, I hope someone can help.

I have a Dtae&time column with 04-07-2019 15:17:10.
I only need the date (preferably as 04.07.2019).
I have tried to simply reformat the date to another format but that does not work.

Any ideas?

Thanks in advance.

 

[etaf]

HELTAL

 

[ChrisCpH]

Ok, I’m need to be off to be a Dad for the rest of the evening before the kids forget what I look like.

The irritating thing is that I have always worked in Excel in English but was forced to switch the Danish interface with 365 to use European number formatting.

Thanks for your help, will try again in the morning.

 

[Peter_SSs]

Another option to consider is Text To Columns, either manually of by vba. By vba, you could try this with a copy of your data.
It assumes date/time in column A, starting in row 2, with the resultant date only going into column B.

Notes:
1. If you want the data to over-write the original and stay in the same column, then omit the blue code.
2. I'm unsure whether a date of 04-07-2019 for you means 4 July or 7 April so you may need to also change the red array. Post back with date details if you need further help with that.

Sub OmitTime()
    Range("A2", Range("A" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 9), Array(3, 9))
End Sub

 

[ChrisCpH]

Thanks, will give it a go.

Yes my data is dd-mm-yyyy hh:mm:ss and I want dd.mm.yy

 

[ChrisCpH]

@Peter I'm new to vba - it seems I need to spend a lot of time learning how to use macros and VBA and right now I have a VAT report to make for the tax submissions. I'll have to go in a manually transcribe all the dates for now and get back to this when I have more time.

Thanks for your help.

 

[Peter_SSs]

A couple of choices:

A. You can do Text To Columns manually in a few clicks as follows:

1. Select the column by clicking the column heading label letter
2. Data ribbon tab -> Text to Columns (in Data Tools group) -> Delimited -> Next -> Mark 'Space' in the Delimiters section and unmark any others -> Next -> Down the bottom in the 'preview' section the first column should be black -> Above that in the Column data format section click 'Date'; and choose the format that the data part is actually in (DMY or MDY) -> click in the 2nd column at the bottom to make it black -> Choose 'Do not import this column (skip)' above -> If there is a 3rd column at the bottom (AM/PM) click it and also 'Do not import' -> Change 'Destination' if you do not want to simply replace your original data -> Finish

B. To run the macro I provided above:

1. With your workbook active press Alt+F11 to bring up the vba window.​

2. In the Visual Basic window use the menu to Insert|Module​

3. Copy and Paste the code provided into the main right hand pane that opens at step 2.​

4. Close the Visual Basic window.​

5. Press Alt+F8 to bring up the Macro dialog​

6. Select the macro 'OmitTime' & click ‘Run’​

7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)​