Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Manchester, Gtr Manchester, England
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula for times (excel 2000)

    I'm trying to put a formula in place that looks at searching criteria in cells that have date and time in. One field has a time for a query being raised. The other box has a completion time. I need to find those where the difference is less than an hour. This gets more complicated as the clock needs to be stopped at 1800 and re-commence at 0700.
    An ideas ? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Formula for times (excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Roadways

    How about using a Formula in a column that would check if there is an hour difference between the two Time Values, then filter on that column. You will get all the None one hour difference or the one Hour difference depending on what you want.

    I don't really understand your question when you say <<< <font color=blue> This gets more complicated as the clock needs to be stopped at 1800 and re-commence at 0700.
    </font color=blue>
    >>>

    Do you have any queries that execute between 1800 and 0700, or there are none?

    Get back to us with a better explanation, please.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula for times (excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    So long as the start and finish times are formated in Excel formats (Date / Time) that should be no problem.

    I assume that the 'times' also include a date?

    If a field is formatted as "date time" e.g. "DD-MMM-YYYY HH:MM:SS"
    Then it is stored as an internal date-time number. The Whole number part is the date - the Fractional part is the time as a proportion of 24 hours.

    Thus an accurate date time difference is simply = EndDAte - StartDate - NightShift
    where Nightshift = Trunc(EndDate-StartDate) * 13/24
    (taking off 13 hours for each day difference in dates). Naturally this does not include weekends as nightshift but a similar technique could be used.

Posting Permissions

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