|
ちょっとベタなやり方ですが考えてみました
※24時間以上のケースがない前提です
参考にしてカスタマイズして下さい
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 = TimeValue("23:59")
Else
NTime = ETime
End If
TTime = STime
Do
Select Case TTime
Case Is > TimeValue("23:29")
TTime = TTime - TimeValue("23:30")
NVal = NVal + 300
NTime = ETime
Case Is >= TimeValue("20:00")
TTime = TTime + TimeValue("0:30")
NVal = NVal + 300
Case Is >= TimeValue("6:00")
TTime = TTime + TimeValue("0:20")
TVal = TVal + 400
Case Else
TTime = TTime + TimeValue("1:00")
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
|
|