cl

回復 30# sistinechapel

Assuming your date in text format of d/m/yyyy stored in cell A1, using date() function, the
parameters of date() are year, month and day. So the first part of value(right()) is to
extract the year part from the string in A1 (i.e. "2012") and change it to number
(i.e. value("2012")). The second part is to extract the month from the string stored in A1 by
using mid() and search() functions. Mid() is to extract the month from the string which
requires 3 parameters. 1st is the location of the string (i.e. A1). 2nd is the starting position
which is the position after the first "/". 3rd parameter is the length of the string which
is calculated by finding the last position of the string (i.e. the last position of "/") minus
the starting position. Finally the 3rd parameter of date() is day which is first 1 or 2 digit
of the string stored in A1 which can be found by using search().

You can try using each function separately to see the intermediate result.

冰之風

原本data 係excel 黎.  格式係 dd.mm.yyyy  同事教我用 ctrl-F 將  "." 轉晒做 "/"

依家係 dd/mm/yyyy   ...
sistinechapel 發表於 2012-11-6 01:33

咁dd.mm.yyyy 是否直接input去Excel呢?  也許upload部份data的Excel file比我地試吓吧！

sistinechapel

problem solved!!!!!!!!!!!!


答案竟然係...  M$ office 2010

2010 識自己食

sistinechapel

想死...

返到公司又唔得...

公司係 office 2010...  屋企係 office 2010 starter 反而得...

想點???

果仁

應該係控制台既日期設定問題, #21 的師兄講得O岩

只係純打幾個日子 , 再用  A-B
以下係SET左  M/D/YYYY 同  D/M/YYYY
我只係用 EXCEL2000 都做到呢樣野
樓主不妨試試啦

sistinechapel

應該係控制台既日期設定問題, #21 的師兄講得O岩

只係純打幾個日子 , 再用  A-B
以下係SET左  M/D/YYYY  ...
果仁 發表於 2012-11-7 11:14

控制台?  公司機冇 admin 權...  

sistinechapel

設定>日期中可選
日/月/年
tseyik 發表於 2012-11-7 09:22

excel要第一下食到佢係日期格式先改到ga!!!!!

31/1/2012  <---  食唔到 (係得我屋企食到)
2012/1/31 <--- 食到