|
▼ハト さん:
>ちょっとベタなやり方ですが考えてみました
>※24時間以上のケースがない前提です
>参考にしてカスタマイズして下さい
>
DateAdd関数を使った方が処理がすっきりしました(^^;
Sub Test_Time()
Dim ws As Worksheet
Dim STime As Date
Dim ETime As Date
Dim NTime As Date
Dim TTime As Date
Dim TVal As Long
Dim NVal As Long
TVal = 0
NVal = 0
Set ws = ThisWorkbook.Worksheets("Sheet1")
STime = TimeValue(ws.Range("A1").Text)
ETime = TimeValue(ws.Range("B1").Text)
If STime > ETime Then
NTime = DateAdd("d", 2, ETime)
Else
NTime = DateAdd("d", 1, ETime)
End If
STime = DateAdd("d", 1, STime)
TTime = STime
Do
Select Case TimeValue(TTime)
Case Is >= TimeValue("20:00")
TTime = DateAdd("n", 30, TTime)
NVal = NVal + 300
Case Is >= TimeValue("6:00")
TTime = DateAdd("n", 20, TTime)
TVal = TVal + 400
Case Else
TTime = DateAdd("h", 1, TTime)
NVal = NVal + 200
End Select
If TTime >= NTime Then Exit Do
Loop
If NVal > 1500 Then NVal = 1500
TVal = TVal + NVal
ws.Range("C1").Value = TVal
End Sub
|
|