105.03.24 第4次上課 (範例-03日期與時間函數.xls)
◎重要概念
日期資料是計算與1900/1/1間隔天數的數值序列，例如：2020/4/29為43950
日期函數的計算都是以這個數值來處理，例如：DATE(2020,4,29)=43950 → DATE(2020,5,1)=43952
◎ VBA 時間日期函數
◎ Excel 時間日期函數
01_日期與時間函數日期產生與格式
TODAY() 不用參數，帶出當日的日期
儲存格自訂格式：預設：2020/4/29 →改為自訂格式，例如：[$-zh-TW]e.m.d(aaa);@ 顯示為：109年4月29日(週三)
DATE(年,月,日)，格式化數值為日期格式
例如：DATE(2016,8,16)→顯示為日期格式 2016/8/16
02_DATEDIF函數使用說明
DATEDIF(起始日期 , 結束日期 , “引數”)→計算間隔數值，利用引數回傳時間格式
| 間隔條件 | 間隔條件-參數 |
| 年數 | Y |
| 月數 | M |
| 天數 | D |
| 忽略年數和天數後的月數 | YM |
| 忽略年數後的天數 | YD |
| 忽略年數和月數後的天數 | MD |
例如：=DATEDIF( DATE(1980,8,15) , DATE(2006,10,12) ,”Y” )
備註：也可以用儲存格指定日期，但儲存格必須為日期格式
03_用DADEDIF計算精確生日
搭配TODAY()
例如：=DATEDIF(DATE(1972,8,5),TODAY(),"Y") & "歲，" & DATEDIF(DATE(1972,8,5),TODAY(),"YM") & "個月又 " & DATEDIF(DATE(1972,8,5),TODAY(),"MD") & "天"
04_將公式用VBA輸出
Public Sub DIF函數()
For i = 3 To 8
r = Cells(i, "L")
Cells(i, "J") = "=DATEDIF(A" & i & ",B" & i & ",""" & r & """)"
Next
End Sub
Public Sub 年齡()
For i = 15 To 19
Cells(i, "H") = "=DATEDIF(B" & i & _
",TODAY(),""Y"")&""歲，""&DATEDIF(B" & i & _
",TODAY(),""YM"")&""個月又""&DATEDIF(B" & i & _
",TODAY(),""MD"")&""天"""
Next
End Sub
備註：程式碼斷行，加 _ 再 enter
05_用EDATE函數計算月與自訂年季周計算函數
1)EDATE(起始日期 , 間隔日數值 )
例如：=EDATE(DATE(2016,8,16) , 3)→2016/11/16
2)自訂年季周計算函數，使用 DateAdd( )
備註：Excel沒有這個內建函數
引數說明：
| 設定 | → | 說明 |
| yyyy | → | 年 |
| q | → | 季 |
| m | → | 月 |
| y | → | 年中的日 |
| d | → | 日 |
| w | → | 工作日 |
| ww | → | 週 |
| h | → | 時 |
| n | → | 分 |
| s | → | 秒 |
例如：DateAdd(“m”, 1, “31-Jan-95”) 表示在31-Jan-95增加1個月
Public Function EDate_Y(日期, 幾年)
EDate_Y = DateAdd("yyyy", 幾年, 日期)
End Function
Public Function EDate_Q(日期, 幾季)
EDate_Q = DateAdd("q", 幾季, 日期)
End Function
Public Function EDate_W(日期, 幾周)
EDate_W = DateAdd("ww", 幾周, 日期)
End Function
Public Function ETime_H(時間, 幾時)
ETime_H = DateAdd("h", 幾時, 時間)
End Function
Public Function ETime_M(時間, 幾分)
ETime_M = DateAdd("n", 幾分, 時間)
End Function
Public Function ETime_S(時間, 幾秒)
ETime_S = DateAdd("s", 幾秒, 時間)
End Function
06_如何快速製作年曆(EDATE與MONTH與IFERROR)
MONTH(serial_number)→回傳時間數值所在的月份
IFERROR(value, value_if_error)→判斷(value)是否有誤，有誤時執行程序(value_if_error)
1)先建立第一天，例如：2020/1/1(A2)，之後以EDATE( )建立2月(B2)至12月(L2)的第一天
儲存格為日期格式
A2=2020/1/1 B2=EDATE(A2,1) C2=EDATE(B2,1) ~~L2=EDATE(K2,1)
2)建立第二天，配合MONTH與IFERROR判斷下一天是否同個月份
A3=IFERROR(IF( MONTH(A2)<>MONTH(A2+1) , “” , A2+1 ) ,””) ，向左、向右填滿即可
因為是自己手動拉選自動填滿，所以不會有超出範圍的問題
3)效果
07_快速製作年曆VBA解說
利用外迴圈(處理月份)+內迴圈(日期)
Public Sub 年曆()
'1.輸入第一天日期
A = Application.InputBox("請輸入年份!!", Type:=1)
Range("A2") = A & "/1/1"
'2.產生2月以後的第1天
For i = 2 To 12
D = DateAdd("m", i - 1, Range("A2"))
Cells(2, i) = D
Next
'3.產生日期
'外迴圈-1-12月
For j = 1 To 12
'內迴圈-每月的第2天
For i = 3 To 32
'新增的日期 Cells(i - 1, j)
D = Cells(i - 1, j) + 1
'加1之後的日期
'因為日期在excel是數值
'與加1之後的日期比較月份
If Month(Cells(i - 1, j)) = Month(D) Then
'MsgBox (D)
'MsgBox (Cells(i - 1, j))
'MsgBox (Month(Cells(i - 1, j)))
'MsgBox (Month(D))
Cells(i, j) = D
Else
'假如加1之後進到下個月分則跳出(內)迴圈
Exit For
End If
Next
Next
End Sub
08_星期六日設定格式化條件與VBA程式撰寫說明
利用 Weekday() 逐一判斷，星期六為7；星期日為1
Public Sub 格式化()
For i = 2 To 32
For j = 1 To 12
If Cells(i, j) <> "" Then
'星期六
If Weekday(Cells(i, j)) = 7 Then
Cells(i, j).Font.Color = RGB(0, 255, 0)
End If
'星期日
If Weekday(Cells(i, j)) = 1 Then
Cells(i, j).Font.Color = RGB(255, 0, 0)
End If
End If
Next
Next
End Sub
Public Sub 清除格式化()
For i = 2 To 32
For j = 1 To 12
If Cells(i, j) <> "" Then
Cells(i, j).Font.Color = RGB(0, 0, 0)
End If
Next
Next
End Sub
09_其他日期函數：VBA 與 Excel 相同函數也可能有用法上的差異
WEEKDAY()，VBA沒有引數，只能顯示1種日期表示1~7：星期日~星期六
NETWORKDAYS()，VBA沒有
WORKDAY()，VBA沒有