其它 - Excel 日期時間轉換

[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)

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