Simple function made difficult

Bare with me on this one...I simply need to subtract A1 from A2 and have the
value appear in A3. All three cells are formatted as TIME.

Here comes the complicated part.

Before subtracting A1 from A2, I need to first calculate the values of A1
and A2 using the following functions within cell A3:

MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)
MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)

Then all I need to do is subtract A1 from A2.

Example

A1 = 5:33 AM
A2 = 3:09 PM

without the MOD function A2-A1 = 9:36
With the MOD function A2-A1 = 9:45

How can I incorporate the MOD function in cell A3 which already contains
=A2-A1 ?

this, in a3, doesn't work?

=MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)-MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)

--


Gary


"GreenMonster" <jwinder@petitindustries.com> wrote in message
news:E8Xre.12960$g5.12421@twister.nyroc.rr.com...
> Bare with me on this one...I simply need to subtract A1 from A2 and have
> the value appear in A3. All three cells are formatted as TIME.
>
> Here comes the complicated part.
>
> Before subtracting A1 from A2, I need to first calculate the values of A1
> and A2 using the following functions within cell A3:
>
> MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)
> MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)
>
> Then all I need to do is subtract A1 from A2.
>
> Example
>
> A1 = 5:33 AM
> A2 = 3:09 PM
>
> without the MOD function A2-A1 = 9:36
> With the MOD function A2-A1 = 9:45
>
> How can I incorporate the MOD function in cell A3 which already contains
> =A2-A1 ?
>
>
>
>
>
>

One way:

=FLOOR(A2+7/1440,1/96) - FLOOR(A1+7/1440,1/96)

or, with one less math operation

=(FLOOR(A2*1440+7,15) - FLOOR(A1*1440+7,15))/1440



In article <E8Xre.12960$g5.12421@twister.nyroc.rr.com>,
"GreenMonster" <jwinder@petitindustries.com> wrote:

> Bare with me on this one...I simply need to subtract A1 from A2 and have the
> value appear in A3. All three cells are formatted as TIME.
>
> Here comes the complicated part.
>
> Before subtracting A1 from A2, I need to first calculate the values of A1
> and A2 using the following functions within cell A3:
>
> MOD(INT((A1+TIME(0,7,0))*24*4)/24/4,1)
> MOD(INT((A2+TIME(0,7,0))*24*4)/24/4,1)
>
> Then all I need to do is subtract A1 from A2.
>
> Example
>
> A1 = 5:33 AM
> A2 = 3:09 PM
>
> without the MOD function A2-A1 = 9:36
> With the MOD function A2-A1 = 9:45
>
> How can I incorporate the MOD function in cell A3 which already contains
> =A2-A1 ?