# Thread: Formula for times (excel 2000)

1. ## 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. ## 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

3. ## 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
•