Delete Time from a Date & Time Field

[nemo1478]

How do I delete the times from the column of Date and Time. I have tried and tried to change the format of the cells but for some reason it just doesn't work.

All the times are after the year 2020 so if I can find a way to delete everything after 2020 that might work too.

 

[sandy666]

maybe text2columns with space as delimiter then delete time column

 

[Norie]

You could use this formula to only return the date part.

=INT(C3)

Alternatively:

1 Select the data.
2 Goto Data>Text to columns...,
3 Select Delimited, select Space as a delimiter
4 On the next step select MDY for the Column data format of the first column and Do not import for the second.
5 Click Finish.

 

[nemo1478]

I don't know what you mean by that.

 

[sandy666]

Split text into different columns with the Convert Text to Columns Wizard - Microsoft Support

Learn how to take the text in one or more cells, and split it out across multiple cells by using Excel functions. This is called parsing, and is the opposite of concatenate. Watch more in this video.

support.microsoft.com

 

[nemo1478]

Thank you so much! It worked.

 

[sandy666]

You are welcome
Glad we could help

 

[Peter_SSs]

Thank you so much! It worked.

If it was the Text to Columns then I would suggest 2 changes as there is no need to subsequently delete the time part(s).

With your sample data and using a space delimiter you would (by default) have 2 extra columns to delete, though that can be avoided - see below. I would use Text to Columns with fixed width.
For me , that comes up like this

Click Next and in that window click in the 2nd column to make it black and choose 'Do not import column' then Finish

You could just as well use Delimited with space as delimiter but there would then be two columns to 'Do not import column'