Formula to convert date without the time

[JJFletcher]

Hello All,

I am having a problem removing the time from the dates in a column that has over 50K records.

I tried formatting the column but found that the zero's do not go away unless there is a space between the date and the time 0:00... IE: 1/1/20230:00.

When I format the column to a standard date *03/14/2012 the time is still there... The 0:00 only goes away and is converted to just a date when the date and time are separated by a space. Has anyone experienced this before and

what is a possible solution.

It is not possible for anyone to individually put a space manually for over 50K records.

Any thought would be appreciated.

John

[TMS]

1/1/20230:00 is NOT a date and time without a space, it is Text.

One example is not enough to permit anyone to develop a solution. You need to upload a sample that has ALL the typical entries. 1/1/2023, 1/11/2023, 21/1/2023, 31/10/2023, and similarly for time values.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[bebo021999]

Use a helper column (column B) to convert column A into date, then copy/ paste value back to A

Please Login or Register  to view this content.

with these samples.

Capture.JPG

[JJFletcher]

Hi bebo021999,

Outstanding solution - works like a charm!!!!

Thanks so much for the solution

Regards,

John

[TMS]

Glad you have a solution. Clearly some folk have more imagination than me. Hope it caters for all the options.


Thanks for the rep.