Changing Dates in a Column ... and more lol

[dave1545]

I wanted to change the date format in a column. There are a few issues; first is that presently, the column that contains dates, also has a time in each entry. So, I do not know how to remove the times, if they can be at all. Second, is that the date is presently not showing as yyyy-mm-dd. I followed the prompts to changing them to that, however it di not happen (!?) Is it because there is a time involved? There are 5000 rows, so I cannot do this manually. All suggestions and definitely a walk through would be awesome. Thank you.

[#2]

There were formatting options to make the timestamps not display, but if they aren't working I have a possible clunky option for you. Make a new column and us =Mid, =LEFT or =RIGHT. I am not sure where the time stamp is, but let's say it's before the date. In that case you would use the formula =RIGHT(cell reference you string is currently in, 6). The '6' is the number of characters you want to pull - you will have to adjust this based on how your data is structured.

Not a great solution, but it might be faster than digging more... Hope you figure it out!

[#3]

@ChReed ah, I can give that a go. What about Excel not reformatting the column date? Do you think it is because of the time being in the column?

[#4]

I don't think so, as I have read documentation stating this should work BUT something may have changed there that I am not aware of. Wish I could be more specific!

[#5]

@dave1545 

Most probably you have texts which looks like dates. Not dates which in Excel are actually numbers.

Applying date (or number) format to text won't convert values to dates, it still will be text.

If without time, you have the text like "30/07/2024"  you need to convert it first with Text to Columns (or by formula) into the date, next apply desired date format.

If text where date with time, "30/07/2024 2:30PM" first date shall be separated by formula, e.g. =LEFT(A1, FIND(" ",A1)-1 ).

If you have datetime (not text), e.g. 2024-07-30 14:30, you may apply any date format to show only the date, but value still will be datetime.

If not only to show, but have dater value without time, that by formula, e.g. =INT(A1). Applying desired date format to the result.