Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How does one handle rollovers when doing subtracti

    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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: How does one handle rollovers when doing subtracti

    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

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: How does one handle rollovers when doing subtracti

    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:

    <pre> 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
    </pre>

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

    Gary

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How does one handle rollovers when doing subtracti

    Won't the DateAdd function do this correctly?

    Mike

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How does one handle rollovers when doing subtracti

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •