Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Upstate NY, New York, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calcuating time difference (Word 97/VBA)

    I'm having a problem calculating times using DateDiff. My project is an employee timesheet. For example, the user will enter the time they arrived in the morning and when they left for lunch. I need to calculate the difference between these two. Not a problem if they leave at noon, but if they arrive at 8:00 a.m. and leave at 1:00 p.m., I get a negative value. I know I'm omitting something necessary...perhaps a lot of things, since I'm pretty much a novice in this area.

    Here's what I wrote:

    ActiveDocument.Variables.Add Name:="MTotal", Value:=Format(DateDiff("h", TB3, TB4), "HH:mm")

    I assume I would first have to determine if TB3 is am or pm; likewise for TB4.

    Any assistance would be greatly appreciated.

  2. #2
    New Lounger
    Join Date
    Jul 2002
    Location
    Princes Risborough, Buckinghamshire, England
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calcuating time difference (Word 97/VBA)

    You're on the right track in choosing DateDiff. Ideally, the two date arguments supplied to DateDiff should be of the Date data type, but reasonable conversions will be carried out for you. Try some experiments in VBA's immediate window along the following lines: (At the risk of teaching you to suck eggs, you only need to type or copy the lines starting "?" - when you hit the return key, the value of the expression after "?" will be displayed. The stuff after ' -- below is just my comments.)

    ?DateDiff("h", "8:00", "20:00")
    12
    ?DateDiff("h", "8am", "8pm")
    12
    ' -- so far this makes sense, but look at these two:
    ?DateDiff("h", "8:01", "20:59")
    12
    ?DateDiff("h", "8:59", "20:01")
    12
    ' -- the minutes are IGNORED if you use "h". So let's use "n" instead,
    ' ("m" means months, so minutes are indicated by "n")
    ?DateDiff("n", "8am", "8pm")
    720
    ?DateDiff("n", "8:01", "20:59")
    778
    ?DateDiff("n", "8:59", "20:01")
    662
    ' -- the 116 minute difference between these two durations is
    ' reflected correctly this time.

    '-- now, to get the interval formatted nicely instead of working out how many
    'hours and minutes there are in 662 minutes, remember that the Date datatype
    'is really an integer indicating a date, plus a number between 0 and 1 indicating
    'the time of day on that date. So divide the time span in minutes by the number
    of minutes in a day, and format it:

    ?Format(DateDiff("n", "8:59", "20:01")/(24*60),"hh:nn")
    11:02
    ' -- Job done

    If you double-click "DateDiff" in the immediate window and hit F1, you'll get the official detail on how this function works.

    You may need to read the small print about how DateDiff reacts to Regional Settings if your solution needs to work in a wide variety of countries.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Upstate NY, New York, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calcuating time difference (Word 97/VBA)

    Wow! That's awesome... I'll give it a shot. It certainly seems to make sense to me at first glance. Thank you so much for taking the time to reply. I'll let you know how it works out. Much appreciation <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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