How does one handle rollovers when doing subtracti

Topic

New Reply

How does one handle rollovers when doing subtraction with time?

I find that when adding time, VBA handles rollovers at the 24-hour mark gracefully; but when i subtract hours, VBA gives me weird results;

Here is some code:

‘ subtract 12 hours, if resuting time is larger than current time then a 24-hour rollover occurred
‘ show current time and calculated time
If (TimeValue(TimeSerial(Hour(Time) – 12, Minute(Time), Second(Time)))) > TimeValue(Time()) Then
MsgBox “rollover”
MsgBox TimeValue(Time()) & ” ” & TimeValue(TimeSerial(Hour(Time) – 23, Minute(Time), Second(Time)))
End If

If the current time is 10:20:00 AM, I might get something like 12:28:04 PM (which is totally wrong);

how should i do time subtraction properly to handle the 24-hour rollover?

Thank you!

–llyal

Reply | Quote

Replies

To see if we are less than 12 hours into a day, try this, which should return True before 12:00:01 PM and False thereafter. You might have been presenting only a hypothetical scenario, but otherwise, this should do the trick:

If DatePart(“d”, DateAdd(“h”, -12, Now())) DatePart(“d”, Now()) Then
‘i.e., if the day of 12 hours ago is not the current day
MsgBox “It must be morning.”
Else
MsgBox “It must not be morning.”
End If

Reply | Quote

The DateAdd might be the way to go if i want to subtract a specific time (hh:mm:ss); I do see alot of conditional loops being made for each time type (hh, mm, ss) to test if the subtraction causes a 24-hour rollover into the previous day or not, so that the date could be decremented;

-Thanks for the help!

–llyal

Reply | Quote

Hi,
Here’s some code I worked out recently, for a similar problem. I don’t know if it’s relevant for what you need (it is a lot simpler because the time increments were set at increments of 15 minutes), anyway:

    If nToTime > nFromTime Then
        nTotTime = Format(nToTime - nFromTime, "00.00")
    Else 'it spans a midnight:
        If nFromTime = "0.00" And nToTime = "0.00" Then
            nTotTime = "0.00"
        Else:
            nTotTime = Format(nToTime + 24 - nFromTime, "00.00")
        End If
    End If

(the nToTime and nFromTime values were derived from entries in Word form fields, and were of Single data type)

Gary

Reply | Quote

Won’t the DateAdd function do this correctly?

Mike

Reply | Quote