20點--請問excel輸入ㄧ串數字便可自動轉換成日期時間?

Question

比如說我想在a1輸入060121空白16(EX:060121 16),能讓系統自動辨別這是時間格式,或是讓a2出現06/01/21 16:00,要用哪種公式呢?(已經試過把格式改為自訂yymmdd hh,但似乎不行這樣設,系統不能分辨,ㄧ定要設成yy/mm/dd hh這樣才行,但是不想說輸入日期時間還要打/符號,不知有沒有辦法能這麼做呢??)

Dear 杰:您的方法確實可行,但是我想做兩個日期加減,跨月就不行了....(對excel而言,他不是個正確的日期格式)

Dear crdotlin:程式碼放在對應模組..是哪裡呢??假設我A欄輸入060121 16,B欄要顯示06/01/21 16:00(必須為時間格式),該怎麼做呢

Answer 1

在Excel中，時間(日期)序數[1]是代表1900年1月1日，因此不能在同一儲存格內直接以輸以其他數字的方式轉換為日期。用函數轉換倒是可行。請在A2儲存格輸入以下的公式：=TEXT(DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))+TIME(RIGHT(A1,2),0,0),"yy/mm/dd hh:mm")(以上是錯誤試範，先不要輸入。)但因為所輸入的年只有2位數，在轉換時，會被視為1906年，所以修改如下：=TEXT(DATE(20&LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))+TIME(RIGHT(A1,2),0,0),"yy/mm/dd hh:mm")試過可以做運算，但不知版主所需的輸出格式為何，等版主補充時，再加上該部分的解法。簡單說明第二個公式的原理：用TXET()函數來決定最後的輸出格式。用DATE()函數配合LEFT()、MID()函數來求出該日期的序列值。用TIME()函數配合RIGHT()函數來求出時間的序列值。將DATE()與TIME()所求出的序列值相加後，就可得到可供計算的時間序列值。用[&]把[20]加在用LEFT()所得到A1儲存格中最左2位數字，可得到[2006]，以避免程式誤判為1906年。想對函數有進一步的認識，請利用Excel的說明。

Answer 2

請將程式碼放在對應工作表的模組裡
假設A蘭要轉換
Private Sub Worksheet_Change(ByVal Target As Range)
Dim theStr As String
With Target.Cells(1)
If .Column <> 1 Then Exit Sub
theStr = .Text
Application.EnableEvents = False
.Value = VBA.DateSerial(Left(theStr, 2), Mid(theStr, 3, 2), Mid(theStr, 5, 2))
.Value = .Value + VBA.TimeSerial(Right(theStr, 2), 0, 0)
Application.EnableEvents = True
End With
End Sub

Answer 3

我的方法
在a1的儲存格格式 自訂為 0#"/"##"/"## ##!:"00"

只要你在a1打上06012116時 為顯示 06/01/21 16:00