Create formula-if value A falls between values B and C in time for

[Guest]

Struggling how to create a formula to check if a value falls between two
others where one value is another calculation. My data is:

A B C D
1 07:00:00 07:10:00 07:30:00 08:15:00

I want to say " if B1 falls between A1 and A1+15 minutes, then subtract A1
from D1.
There are other conditions I have written that this will be added to.
Look forward to an elegant solution.

 

[PCLIVE]

Maybe one way:
=IF(AND(B1>=A1,B1<=(A1+15)),D1-A1)

HTH,
Paul

 

[JE McGimpsey]

One way:

XL times are stored as fractional days. Since there are 1440 minutes in
a day, this will work:

=IF(MOD(B1-A1,1)<=15/1440, MOD(D1-A1,1), "something else")

The MOD(xxx,1) allows for times to span midnight.

 

[JE McGimpsey]

Did you try it?

This will always return TRUE if B1 is entered as a time, since times are
less than 1, and A1+15 is > 1

 

[PCLIVE]

Sorry JE. You're correct. I skimmed through this one fast and did not pay
attention to the fact that these were times.
Thanks for the correction.

Paul

 

[Guest]

Worked as designed. Now to link it to the other statements.

First experience with this discussion group and could not be more pleased.

Thanks all