其它 - Excel 日期時間轉換

leowang · 2012-12-22, 01:44 AM

--------------------
閱讀本主題的最佳解答
--------------------

有沒有好方法可以用函式將日期時間轉換成下圖的那樣
"將B儲存格轉換成C儲存格那樣"

http://a.imageshack.us/img541/3193/123wu.jpg

getter · 2012-12-25, 08:12 PM

引用:

作者: leowang

有沒有好方法可以用函式將日期時間轉換成下圖的那樣
"將B儲存格轉換成C儲存格那樣"

原始的來源格式，似乎是 NOW() 的函數
因此假設
A1 儲存格為 =NOW()
B1 儲存格為 =MONTH(A1)&DAY(A1)&CHAR(10)&HOUR(A1)&MINUTE(A1)
並且將 B1 儲存格的「儲存格格式」中的自動換行勾選起來，應該就可以做到如上所示的
效果 ...

當然若是將兩者的公式合併的話就是
=MONTH(NOW())&DAY(NOW())&CHAR(10)&HOUR(NOW())&MINUTE(NOW())

Excel 函數解說
Now 取得系統目前的時間，只要重新開檔、重新入 =NOW() 的儲存格，內容就會被更新
MONTH 取得日期格式中「月的部份」1～12。
DAY 取得日期格式中「日的部份」1～31。
HOUR 取得時間格式中「小時的部份」0～12 或 0～23。
MINUTE 取得時間格式中「分鐘的部份」0～59。
CHAR(10) 取得換行符號，在儲存格有自動換行下，當有換行符號時，在該符號位置會換行。

--------------------------------------------------------------------------
如果這是純文字的格式的話，如
A3 儲存格為 '2012/12/21 07:33:00 PM
B3 儲存格為 =REPLACE(MID(A3,6,FIND(" ",MID(A3,6,6))),FIND("/",MID(A3,6,5)),1,"")&CHAR(10)&REPLACE(LEFT(RIGHT(A3,13),5),FIND(":",RIGHT(A3,13)),1,"")
並且將 B2 儲存格的「儲存格格式」中的自動換行勾選起來，應該就可以做到如上所示的
效果 ...

Excel 函數解說
REPLACE 取代指定位置的字元或字串。
RIGHT 從右方開始取得指定字數的字元或字串。
LEFT 從左方開始取得指定字數的字元或字串。
MID 從指定從左方第幾位起開始取得指定字數的字元或字串。
FIND 從指定從左方第幾位起開始找尋指定字元或字串，並回傳在字串中的位置編號(Index)。
CHAR(10) 取得換行符號，在儲存格有自動換行下，當有換行符號時，在該符號位置會換行。

不然就要使用 VBA 來作格式轉換了 ...

leowang · 2012-12-29, 12:33 PM

我自己研究的結果只搞定月、日而已
但是時間方面一直搞不定
比如09:00 轉出來只有 90

http://a.imageshack.us/img600/4448/12695175.png

參考了getter大大的函數好像也一樣

getter · 2012-12-29, 06:25 PM

引用:

作者: leowang

我自己研究的結果只搞定月、日而已
但是時間方面一直搞不定
比如09:00 轉出來只有 90

參考了getter大大的函數好像也一樣

會不會動到了一些不知道的設定 ... 剛開始的迪西也是有 90 出現
後來就不會了 ...

不然試著看看迪西測試的檔案看看
http://www.ziddu.com/download/21221300/123.rar.html

leowang · 2012-12-31, 04:42 PM

引用:

作者: getter

會不會動到了一些不知道的設定 ... 剛開始的迪西也是有 90 出現
後來就不會了 ...

不然試著看看迪西測試的檔案看看
http://www.ziddu.com/download/21221300/123.rar.html

我的時間是用Key in的不是用NOW()函數
也就是說我Key 2012/12/31 09:00 Excel的欄位會自己判斷為 2012/11/21 09:00:00 AM 但是顯示出來會是 2012/12/31 9:00 (因為儲存格格式選的是時間)

http://imageshack.us/a/img221/425/123oh.png

http://imageshack.us/a/img22/320/456xe.png

所以要把 2012/12/31 9:00 轉成 1231 0900

getter · 2012-12-31, 08:09 PM

用 =MONTH(A1)&DAY(A1)&CHAR(10)&HOUR(A1)&MINUTE(A1)
改用手動輸入，使用上述一樣的時間格式 ... 可以正常顯示 ...

A1 儲存格為 2012/12/31 08:14:12 PM
B1 儲存格為 =HOUR(A1)
B1 儲存格格式『日期』
顯示就會變成 1900/1/20 00：00

B1 改成 =HOUR(A1)&MINUTE(A1)
顯示就會變成 2014

後來發現單獨使用 MINUTE 結果跟 HOUR 是一樣的
會變成哪種顯示有點怪的狀況跟 B1 儲存格格式有關系，
若單獨使用的話， B1 儲存格格式為『日期』時，會變成
把輸出的結果填到日期的『日』上面。若改成文字、數值
、G/通用等格式，則會單純的顯示該值，如 8。
當公式完全後，會各自把函數的值填到相對的位置，此格式
為日期文字、數值、G/通用等格式，都不會變動 ...

還有一種簡單的方式就是使用[自訂儲存格式]
如 mmdd hhmm 可以得到正確的顯示結果，但不會斷行 ....

若怎麼改都是很奇怪的話，只能說不曉得原因在哪裡了 ...

leowang · 2013-01-02, 01:34 AM

引用:

作者: getter

用 =MONTH(A1)&DAY(A1)&CHAR(10)&HOUR(A1)&MINUTE(A1)
改用手動輸入，使用上述一樣的時間格式 ... 可以正常顯示 ...

A1 儲存格為 2012/12/31 08:14:12 PM
B1 儲存格為 =HOUR(A1)
B1 儲存格格式『日期』
顯示就會變成 1900/1/20 00：00

B1 改成 =HOUR(A1)&MINUTE(A1)
顯示就會變成 2014

後來發現單獨使用 MINUTE 結果跟 HOUR 是一樣的
會變成哪種顯示有點怪的狀況跟 B1 儲存格格式有關系，
若單獨使用的話， B1 儲存格格式為『日期』時，會變成
把輸出的結果填到日期的『日』上面。若改成文字、數值
、G/通用等格式，則會單純的顯示該值，如 8。
當公式完全後，會各自把函數的值填到相對的位置，此格式
為日期文字、數值、G/通用等格式，都不會變動 ...

還有一種簡單的方式就是使用[自訂儲存格式]
如 mmdd hhmm 可以得到正確的顯示結果，但不會斷行 ....

若怎麼改都是很奇怪的話，只能說不曉得原因在哪裡了 ...

用 MONTH(A1)&DAY(A1)&CHAR(10)&HOUR(A1)&MINUTE(A1) 函數的話
遇到0的話就不會顯示出來的比如0900 只會顯示90

如果用改變顯示格式方式的話改變的是顯示方式雖然可以達到要的目的
但如果與Word配合使用的話還是會顯示原本的時間格式

getter · 2013-01-02, 07:30 PM

引用:

作者: leowang

用 MONTH(A1)&DAY(A1)&CHAR(10)&HOUR(A1)&MINUTE(A1) 函數的話
遇到0的話就不會顯示出來的比如0900 只會顯示90
如果用改變顯示格式方式的話改變的是顯示方式雖然可以達到要的目的
但如果與Word配合使用的話還是會顯示原本的時間格式

遇到0的話就不會顯示出來的比如0900 只會顯示90
迪西這邊的不會說 ... 您那個看來是 office 2010 的樣子

或考慮換成 2003 或其他種的 Office 試試看看 ...
或者是把 Office 更新看看 ...

可以問一下版上其他人也會一樣嗎？

leowang · 2013-01-02, 10:40 PM

引用:

作者: getter

遇到0的話就不會顯示出來的比如0900 只會顯示90
迪西這邊的不會說 ... 您那個看來是 office 2010 的樣子

或考慮換成 2003 或其他種的 Office 試試看看 ...
或者是把 Office 更新看看 ...

可以問一下版上其他人也會一樣嗎？

就大大的圖所顯示的09:18 就顯示918
而不會顯示0918

getter · 2013-01-03, 10:51 PM

引用:

作者: leowang

就大大的圖所顯示的09:18 就顯示918
而不會顯示0918

這種的喔 ... 原因是在於函數本身的回傳啦 ...
當各自組合之後的顯示概念上的笑話 ...
這樣的話，在年月的部份也會鬧出相同的效果出來 ...

主要原因是 Excel 函數本身的回傳值，只要是個位數都會只有 1位數
的回傳，舉例：
DAY(1/9) = 9 但不會顯示 09
MONTH(1/9) = 1 但不會顯示 01

當以字串組合後就會變成 19 ...

不信 ... 去改變月日就知道了 ...

若是先前的公式，若是 2013/01/01 03:03 AM 好了
就會被翻譯成 11 33，這不是函數出錯，是經過組合後的顯示邏輯的格式錯
誤 ...

這不是函數的錯，因為是回傳成數字。不是特定格式的顯示文字，所有的函數
都是這樣 ...

怎麼解決，設法補足缺乏的那的 0 的那 1位數，使其變成 2位數，在經組合之後
就會是想要的結果 ...

怎麼補 ... 有兩種方式 1個是使用函數 IF，另一個使用 VBA 自建函數來作業
這裡先提供第一種方式 ... 比較簡單 ...

原理很簡單 ... 先用 IF 判斷單一函數的回傳值是不是個位數的條件，是的話
就補個 0。簡單喔

這裡以月先示範一下 IF(MONTH(A1)<10,0&MONTH(A1),MONTH(A1))
這樣的話 1 就會變成 01 了

時間的話就比較麻煩了 ... 因為有 12 hr 或 24hr
24hr
=IF(MONTH(A1)<10,0&MONTH(A1),MONTH(A1)) &
IF(DAY(A1)<10,0&DAY(A1),0&DAY(A1)) &CHAR(10)&
IF(HOUR(A1)<10,0&HOUR(A1),HOUR(A1)) &
IF(MINUTE(A1)<10,0&MINUTE(A1),MINUTE(A1))

12hr
=IF(MONTH(A1)<10,0&MONTH(A1),MONTH(A1))&
IF(DAY(A1)<10,0&DAY(A1),0&DAY(A1))&CHAR(10)&
IF(MOD(HOUR(A1),12)=0,12,IF(HOUR(A1)<10,0&HOUR(A1),IF(HOUR(A1)<22,0&HOUR(A1)-12,HOUR(A1)-12)))&
IF(MINUTE(A1)<10,0&MINUTE(A1),MINUTE(A1))&
IF(HOUR(A1)<12,"AM","PM")

getter · 2013-01-04, 12:22 AM

語法:

Function Df12hr(ByVal DateTime) As Variant
 Dim YearX, MonX, DayX, HrX, MinX, SecX, APM, Out As Variant

 YearX = Year(DateTime)  '取得年份數值
 MonX = MONTH(DateTime)  '取得月份數值
 DayX = Day(DateTime)    '取得日期數值
 HrX = Hour(DateTime)    '取得小時數值
 MinX = Minute(DateTime) '取得分鐘數值
 SecX = Second(DateTime) '取得秒鐘數值
  
 If HrX < 12 Then        '取得 AM 或 PM
    APM = "AM"
 Else
    APM = "PM"
 End If
 
 If MonX < 10 Then  '月補 0 位數
    Out = "0" & MonX
 Else
    Out = MonX
 End If
 
 If DayX < 10 Then  '日補 0 位數
    Out = Out & "0" & DayX
 Else
    Out = Out & DayX
 End If

 Out = Out & vbCrLf  '換行符號
 
 If HrX > 11 Then '24hr 轉 12hr
    HrX = HrX - 12
 End If
 If HrX = 0 Then
    Out = Out & 12
 ElseIf HrX < 10 Then  '時補 0 位數
        Out = Out & "0" & HrX
    Else
        Out = Out & HrX
    End If
 
 If MinX < 10 Then  '分補 0 位數
    Out = Out & "0" & MinX
 Else
    Out = Out & MinX
 End If

 Df12hr = Out & APM
End Function


Function Df24hr(ByVal DateTime) As Variant
 Dim YearX, MonX, DayX, HrX, MinX, SecX, APM, Out As Variant

 YearX = Year(DateTime)  '取得年份數值
 MonX = MONTH(DateTime)  '取得月份數值
 DayX = Day(DateTime)    '取得日期數值
 HrX = Hour(DateTime)    '取得小時數值
 MinX = Minute(DateTime) '取得分鐘數值
 SecX = Second(DateTime) '取得秒鐘數值
  
 If HrX < 12 Then        '取得 AM 或 PM
    APM = "AM"
 Else
    APM = "PM"
 End If
 
 If MonX < 10 Then  '月補 0 位數
    Out = "0" & MonX
 Else
    Out = MonX
 End If
 
 If DayX < 10 Then  '日補 0 位數
    Out = Out & "0" & DayX
 Else
    Out = Out & DayX
 End If

 Out = Out & vbCrLf  '換行符號
 
 If HrX < 10 Then  '時補 0 位數
    Out = Out & "0" & HrX
 Else
    Out = Out & HrX
 End If
 
 If MinX < 10 Then  '分補 0 位數
    Out = Out & "0" & MinX
 Else
    Out = Out & MinX
 End If

 Df24hr = Out '回傳結果
End Function

使用 VBA 編輯器，插入一個「模組」貼上述兩段原始碼 ...

可以參考以下，如何插入一個「模組」並修改名稱
http://server01.lse.com.tw/elearning...330/229001.htm

將上述的原始碼，複製貼上 ...

接著就仿照一般的函數使用
如 =Df24hr(A1) 或是 =Df12hr(A1)

如果換行的話，一樣如之前，要勾選『儲存格格式』裡的自動換行

leowang · 2013-01-04, 12:49 AM

參考了getter大大的公式
我小修改了一下終於符合我的需求

非常的感謝大大的幫助超強的

getter · 2013-01-04, 08:07 PM

一開始有點會錯意 ... 就會想的不一樣 ... 就要花點時間了 ...
用 Excel 內建 IF 函數還是 VBA 自定函數哪個好？

就程式設計的概念是一樣的，但 IF 函數使用上很直覺簡單，但有缺點
若條件一多就會很長，看得很辛苦。當 fx 的公式出錯了，因為公式太
長除錯上，有時就會眼花花，光是弄個括號就會吐血。此外 IF 函數使
用上還有某種數量上的限制，太多一樣會出現找不到原因的錯誤。

VBA 自定函數，可以自行建立自己需要的函數，可以取代那個長到令人吐血
眼花的 fx 公式，缺點有三：1.可能需要一點點的程式基礎，需要能編輯簡單
的 VBA 程式。2.自定函數使用時沒有說明，如果自己寫自己用就還 ok，給人
用有風險，要加說明功能相當複雜。3.因為是 VBA 需要修改 VBA 巨集的安全
性。

leowang · 2013-01-06, 12:12 AM

引用:

作者: getter

一開始有點會錯意 ... 就會想的不一樣 ... 就要花點時間了 ...
用 Excel 內建 IF 函數還是 VBA 自定函數哪個好？

就程式設計的概念是一樣的，但 IF 函數使用上很直覺簡單，但有缺點
若條件一多就會很長，看得很辛苦。當 fx 的公式出錯了，因為公式太
長除錯上，有時就會眼花花，光是弄個括號就會吐血。此外 IF 函數使
用上還有某種數量上的限制，太多一樣會出現找不到原因的錯誤。

VBA 自定函數，可以自行建立自己需要的函數，可以取代那個長到令人吐血
眼花的 fx 公式，缺點有三：1.可能需要一點點的程式基礎，需要能編輯簡單
的 VBA 程式。2.自定函數使用時沒有說明，如果自己寫自己用就還 ok，給人
用有風險，要加說明功能相當複雜。3.因為是 VBA 需要修改 VBA 巨集的安全
性。

我是使用Excel 內建 IF 函數比較容易看得懂也容易修改
VBA的話沒用過所以不是很熟悉它

2012-12-22, 01:44 AM	#1
leowang 長老會員榮譽勳章勳章總數14 UID - 2461 在線等級: 註冊日期: 2002-12-06 文章: 4729 精華: 0 現金: 101679 金幣資產: 668410953 金幣	其它 - Excel 日期時間轉換 -------------------- 閱讀本主題的最佳解答 -------------------- 有沒有好方法可以用函式將日期時間轉換成下圖的那樣 "將B儲存格轉換成C儲存格那樣"
	__________________ 金錢的數量，決定馬子的漂亮硬碟的容量，決定男人的力量製作Mail Logo按這裡
	送花文章: 257, 收花文章: 1161 篇, 收花: 4928 次

2012-12-29, 12:33 PM	#3 (permalink)
leowang 長老會員榮譽勳章勳章總數14 UID - 2461 在線等級: 註冊日期: 2002-12-06 文章: 4729 精華: 0 現金: 101679 金幣資產: 668410953 金幣	我自己研究的結果只搞定月、日而已但是時間方面一直搞不定比如09:00 轉出來只有 90 參考了getter大大的函數好像也一樣

	送花文章: 257, 收花文章: 1161 篇, 收花: 4928 次

2012-12-31, 08:09 PM	#6 (permalink)
getter 管理員榮譽勳章勳章總數19 UID - 6433 在線等級: 註冊日期: 2002-12-08 住址: 天線星球文章: 8157 精華: 0 現金: 19955 金幣資產: 765391 金幣	用 =MONTH(A1)&DAY(A1)&CHAR(10)&HOUR(A1)&MINUTE(A1) 改用手動輸入，使用上述一樣的時間格式 ... 可以正常顯示 ... A1 儲存格為 2012/12/31 08:14:12 PM B1 儲存格為 =HOUR(A1) B1 儲存格格式『日期』顯示就會變成 1900/1/20 00：00 B1 改成 =HOUR(A1)&MINUTE(A1) 顯示就會變成 2014 後來發現單獨使用 MINUTE 結果跟 HOUR 是一樣的會變成哪種顯示有點怪的狀況跟 B1 儲存格格式有關系，若單獨使用的話， B1 儲存格格式為『日期』時，會變成把輸出的結果填到日期的『日』上面。若改成文字、數值、G/通用等格式，則會單純的顯示該值，如 8。當公式完全後，會各自把函數的值填到相對的位置，此格式為日期文字、數值、G/通用等格式，都不會變動 ... 還有一種簡單的方式就是使用[自訂儲存格式] 如 mmdd hhmm 可以得到正確的顯示結果，但不會斷行 .... 若怎麼改都是很奇怪的話，只能說不曉得原因在哪裡了 ... 此帖於 2012-12-31 09:21 PM 被 getter 編輯.

	送花文章: 37855, 收花文章: 6441 篇, 收花: 26052 次

2013-01-04, 12:22 AM	#11 (permalink)
getter 管理員榮譽勳章勳章總數19 UID - 6433 在線等級: 註冊日期: 2002-12-08 住址: 天線星球文章: 8157 精華: 0 現金: 19955 金幣資產: 765391 金幣	語法: Function Df12hr(ByVal DateTime) As Variant Dim YearX, MonX, DayX, HrX, MinX, SecX, APM, Out As Variant YearX = Year(DateTime) '取得年份數值 MonX = MONTH(DateTime) '取得月份數值 DayX = Day(DateTime) '取得日期數值 HrX = Hour(DateTime) '取得小時數值 MinX = Minute(DateTime) '取得分鐘數值 SecX = Second(DateTime) '取得秒鐘數值 If HrX < 12 Then '取得 AM 或 PM APM = "AM" Else APM = "PM" End If If MonX < 10 Then '月補 0 位數 Out = "0" & MonX Else Out = MonX End If If DayX < 10 Then '日補 0 位數 Out = Out & "0" & DayX Else Out = Out & DayX End If Out = Out & vbCrLf '換行符號 If HrX > 11 Then '24hr 轉 12hr HrX = HrX - 12 End If If HrX = 0 Then Out = Out & 12 ElseIf HrX < 10 Then '時補 0 位數 Out = Out & "0" & HrX Else Out = Out & HrX End If If MinX < 10 Then '分補 0 位數 Out = Out & "0" & MinX Else Out = Out & MinX End If Df12hr = Out & APM End Function Function Df24hr(ByVal DateTime) As Variant Dim YearX, MonX, DayX, HrX, MinX, SecX, APM, Out As Variant YearX = Year(DateTime) '取得年份數值 MonX = MONTH(DateTime) '取得月份數值 DayX = Day(DateTime) '取得日期數值 HrX = Hour(DateTime) '取得小時數值 MinX = Minute(DateTime) '取得分鐘數值 SecX = Second(DateTime) '取得秒鐘數值 If HrX < 12 Then '取得 AM 或 PM APM = "AM" Else APM = "PM" End If If MonX < 10 Then '月補 0 位數 Out = "0" & MonX Else Out = MonX End If If DayX < 10 Then '日補 0 位數 Out = Out & "0" & DayX Else Out = Out & DayX End If Out = Out & vbCrLf '換行符號 If HrX < 10 Then '時補 0 位數 Out = Out & "0" & HrX Else Out = Out & HrX End If If MinX < 10 Then '分補 0 位數 Out = Out & "0" & MinX Else Out = Out & MinX End If Df24hr = Out '回傳結果 End Function 使用 VBA 編輯器，插入一個「模組」貼上述兩段原始碼 ... 可以參考以下，如何插入一個「模組」並修改名稱 http://server01.lse.com.tw/elearning...330/229001.htm 將上述的原始碼，複製貼上 ... 接著就仿照一般的函數使用如 =Df24hr(A1) 或是 =Df12hr(A1) 如果換行的話，一樣如之前，要勾選『儲存格格式』裡的自動換行

	送花文章: 37855, 收花文章: 6441 篇, 收花: 26052 次

2013-01-04, 12:49 AM	#12 (permalink)
leowang 長老會員榮譽勳章勳章總數14 UID - 2461 在線等級: 註冊日期: 2002-12-06 文章: 4729 精華: 0 現金: 101679 金幣資產: 668410953 金幣	參考了getter大大的公式我小修改了一下終於符合我的需求非常的感謝大大的幫助超強的

	送花文章: 257, 收花文章: 1161 篇, 收花: 4928 次

2013-01-04, 08:07 PM	#13 (permalink)
getter 管理員榮譽勳章勳章總數19 UID - 6433 在線等級: 註冊日期: 2002-12-08 住址: 天線星球文章: 8157 精華: 0 現金: 19955 金幣資產: 765391 金幣	一開始有點會錯意 ... 就會想的不一樣 ... 就要花點時間了 ... 用 Excel 內建 IF 函數還是 VBA 自定函數哪個好？就程式設計的概念是一樣的，但 IF 函數使用上很直覺簡單，但有缺點若條件一多就會很長，看得很辛苦。當 fx 的公式出錯了，因為公式太長除錯上，有時就會眼花花，光是弄個括號就會吐血。此外 IF 函數使用上還有某種數量上的限制，太多一樣會出現找不到原因的錯誤。 VBA 自定函數，可以自行建立自己需要的函數，可以取代那個長到令人吐血眼花的 fx 公式，缺點有三：1.可能需要一點點的程式基礎，需要能編輯簡單的 VBA 程式。2.自定函數使用時沒有說明，如果自己寫自己用就還 ok，給人用有風險，要加說明功能相當複雜。3.因為是 VBA 需要修改 VBA 巨集的安全性。

	送花文章: 37855, 收花文章: 6441 篇, 收花: 26052 次

相似的主題
主題	主題作者	討論區	回覆	最後發表
如何使AUTOCAD圖檔內資料(材料表)轉換至EXCEL或WORD檔作統計	oldman53	軟體應用問題討論區	2	2004-08-08 11:15 PM
每次開機時間日期都會歸零?	jerry12	一般電腦疑難討論區	3	2003-10-18 01:23 AM
關於xp的日期與時間	光仔	軟體應用問題討論區	0	2003-04-24 12:22 PM

Google 提供的廣告