[Excel VBA] 週數轉換日期)
by 艾鍗學院, 2011-06-09 23:13, 人氣(6069)
''-----週數轉換日期---
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CheckBox1.Value = True Then
End
End If
End
End If
If Not Intersect(Range("B3:CA6"), Target) Is Nothing Then
WeekNum = Cells(2, ActiveCell.Column)
If Not Intersect(Range("3:3"), Target) Is Nothing Then
serial = 40544 + (WeekNum - 1) * 7 ' 40544 means 2011/1/1
End If
If Not Intersect(Range("4:4"), Target) Is Nothing Then
serial = 40544 + (WeekNum - 1) * 7 + 1
End If
End If
''--------------
sWeekDay = Weekday(serial, vbMonday)
Select Case UCase(sWeekDay)
Case Is = "1"
lWeekDay = "星期一"
Case Is = "2"
lWeekDay = "星期二"
Case Is = "3"
lWeekDay = "星期三"
Case Is = "4"
lWeekDay = "星期四"
Case Is = "5"
lWeekDay = "星期五"
Case Is = "6"
lWeekDay = "星期六"
Case Is = "7"
lWeekDay = "星期日"
End Select
'---status--
str1 = Year(serial) & "/" & Month(serial) & "/" & Day(serial) & " (" & lWeekDay & ")"
Range("W23").Value = str1
MsgBox str1
End If
End Sub
str1 = Year(serial) & "/" & Month(serial) & "/" & Day(serial) & " (" & lWeekDay & ")"
Range("W23").Value = str1
MsgBox str1
End If
End Sub
