Forum Discussion

dave1545's avatar
dave1545
Copper Contributor
Jul 29, 2024

Changing Dates in a Column ... and more lol

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 tim...
Excel
Formulas and Functions
training
dave1545's avatar
dave1545
Copper Contributor

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?

 

SergeiBaklan's avatar
SergeiBaklan
MVP
Jul 30, 2024

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.

Resources