其它 - Excel 日期時間轉換

[leowang]

作者: getter

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



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

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

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

[getter]

作者: 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]

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]

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

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

[getter]

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

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

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

[leowang]

作者: getter

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

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

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

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