Formatting error?

[Eloise T]

Please see attached file: Chandoo - Formatting issue.xlsx.

Column G is a list of dates in this format:



The yellow highlighted cells in Column G have a leading space in them.
Trying to figure out which is better: =TRIM(CLEAN(Gx)) OR =CLEAN(TRIM(Gx))
I created Columns P & Q using the formulas.



My conundrum is, why am I unable to format columns P & Q with the Date format like what is used in Column G?

 

[Syedali]

Hi,

Try this

=DATE(YEAR(TRIM(Gx)),MONTH(TRIM(Gx)),DAY(TRIM(Gx)))

 

[John Jairo V]

Hi to all!

The CLEAN and TRIM function are Text Functions. When you use it, the Dates are converted as text.

Try any of this formulas:

=--CLEAN(TRIM(Gx))
=--TRIM(CLEAN(Gx))

Double Negative (--) convert the text into number. Just later give date format.

Blessings!

 

[Eloise T]

Brilliant! Thanks!

 

[Eloise T]

Hi,

Try this

=DATE(YEAR(TRIM(Gx)),MONTH(TRIM(Gx)),DAY(TRIM(Gx)))

Thank you.