Time Calculations

[Matt]

Hello to all
A1 "Start time" 3/13/04 07:0
B1 "Stop Time" 3/13/04 23:0
C1 3/13/04 16:0

I am tryin to determine if the one hour time period preceeding C1 eg. 3/13/04 15:00-16:00, falls between A1 and B1. Curently I am using A1< C1-1/24, which is working. Is there a better way to accomplish this, are there any forseen problems with my approach

Thank

 

[Jerry W. Lewis]

In principle your approach is correct. In practice, most fractions
(including most times) cannot be exactly represented in binary. To be
safe, you should ask whether
A1 < C1-1/24 + epsilon
where epsilon >=1E-15 but small enough to not impact your comparison.

Jerry

Hello to all,
A1 "Start time" 3/13/04 07:00
B1 "Stop Time" 3/13/04 23:00
C1 3/13/04 16:00

I am tryin to determine if the one hour time period preceeding C1

eg. 3/13/04 15:00-16:00, falls between A1 and B1. Curently I am

 

[Matt]

Jerr
Im not quite sure what you are saying but I guess I can use what I have. Wasnt sure if there was a way to do it through funtions or not
Thanks for the respons
Mat

 

[Brettmw23]

Matt, perhaps I'm misunderstanding what your attempting to do, but it
seems to me you're only testing one side of the problem. For instance, your
only checking if it's greater than your start time. When you really need to
find out if it's between your start and end time.

It seems to me that you could use something like:
=If(And(A1<C1,B1>C1),True,False).

--
BMW

-----Original Message-----
Hello to all,
A1 "Start time" 3/13/04 07:00
B1 "Stop Time" 3/13/04 23:00
C1 3/13/04 16:00

I am tryin to determine if the one hour time period preceeding C1 eg. 3/

13/04 15:00-16:00, falls between A1 and B1. Curently I am using A1< C1
-1/24, which is working. Is there a better way to accomplish this, are there
any forseen problems with my approach?

 

[Bob Phillips]

To be more obvious, you could use

A1< C1-TIME(1,0,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello to all,
A1 "Start time" 3/13/04 07:00
B1 "Stop Time" 3/13/04 23:00
C1 3/13/04 16:00

I am tryin to determine if the one hour time period preceeding C1 eg.

3/13/04 15:00-16:00, falls between A1 and B1. Curently I am using A1<
C1-1/24, which is working. Is there a better way to accomplish this, are
there any forseen problems with my approach?

 

[Jerry W. Lewis]

Re the math issue: Suppose
A1 = 7:00am = 7/24
A2 = 6:00am = 6/24
=(A1-A2-1/24)
returns 2.08E-17 instead of zero. This is not a math error.

Neither 7/24 nor 6/24 can be exactly represented in binary (much as
there is no exact decimal fraction representation for 1/3 You start with
0.3333, but you have to stop somewhere ...). The answer of 2.08E-17 is
the exact answer to a math problem that only approximates the math
problem that you intended. You run into these issues on any computer
that does binary floating point arithmetic (almost all software on
almost all hardware). As a result, requiring exact equality on
comparisons from non integer calculations is unrealistic.

Jerry

 

[Matt]

Thanks Jerry, Brett and Bob