Handle Excel "not-date" date
Sometimes, we might get a report with a column, which people "know" they are date but Excel not. It is very common especially as the file is from other notions. We can only use "format" or press "Enter" again in the cell to make Excel know them.
However, Excel often does NOT know them still. I recently found that we can use "Text To Columns" to tell Excel how to recognize them.
BTW, this function can do more than this, and this is most insignificant to me before. Now? I use this function more & more handy.
【中文】有時會收到Excel檔案，尤其以國外寄來的居多，會有一欄「人認得那些是日期」，但Excel卻不認得。有時可用更改格式、或在儲存格內再按次Enter鍵，但不一定有效。最學到一個方法，透過「資料剖析」可以指定讓Excel認識它們。但這功能主要常用的地方卻不是在這狀況，現在懂得如何有效地應用它們。
(1) An example that we know A2 is a date, but Excel doesn't know as we use functions on it.
(2) Go to 「資料」-->「資料剖析」
(3) Set up major methods. There are three steps, but we care the las step in this case. Step03: choose the suspecting "date" column & setup format as "Date" and pick one format most fit our data.
The format is under a simple rule:
Y=year, M=month, D=day. You just have to setup the forward showing sequence of Y, M, and D.
(4) As a result, the value has become as date.
However, Excel often does NOT know them still. I recently found that we can use "Text To Columns" to tell Excel how to recognize them.
BTW, this function can do more than this, and this is most insignificant to me before. Now? I use this function more & more handy.
【中文】有時會收到Excel檔案，尤其以國外寄來的居多，會有一欄「人認得那些是日期」，但Excel卻不認得。有時可用更改格式、或在儲存格內再按次Enter鍵，但不一定有效。最學到一個方法，透過「資料剖析」可以指定讓Excel認識它們。但這功能主要常用的地方卻不是在這狀況，現在懂得如何有效地應用它們。
(1) An example that we know A2 is a date, but Excel doesn't know as we use functions on it.
(2) Go to 「資料」-->「資料剖析」
(3) Set up major methods. There are three steps, but we care the las step in this case. Step03: choose the suspecting "date" column & setup format as "Date" and pick one format most fit our data.
Y=year, M=month, D=day. You just have to setup the forward showing sequence of Y, M, and D.
(4) As a result, the value has become as date.
留言